There seems to be some understandable confusion here.
At "ordinary shooting distances"¹ (for want of a better term), it is convenient to think of the focal length of a lens as a sort of shorthand for magnification. From 6 metres away (20 feet), a 600mm lens will make your subject appear roughly ten times larger than it woud have if you were using a 60mm lens. It's not precisely true except at infinity, but you'd need to go digging into the decimal places to notice the fib, and lensmakers usually round the actual focal length of the lens off to a "familiar" length for labelling, so let's call that close enough for government work (or close enough for jazz, if the government thing offends you).
In macro (and micro) photography, we are generally concerned with fixed amounts of magnification. If a 60mm macro lens and a 600mm macro lens are both labelled as having a "1:1 reproduction ratio", then they will both make the subject the same size on the sensor. That is, they will have the same magnification. The difference between the two lenses is that you could get that magnification using the 600mm lens from roughly ten times as far away from your subject as you could with the 60mm lens. In order to accomplish a 1:1 reproduction, though, both the subject and the sensor (or film) need to be the same distance from the optical centre of the lens, namely twice the focal length.² So you can think of focal length as a convenient shorhand for "working distance" in macro photography, but you do need to keep in mind that longer focal lengths mean larger, heavier and more expensive camera assemblies to get what is essentially the same magnification.
Micro photography is the reciprocal, if you will, of "ordinary" photography. That is, your subject will be closer than twice the focal length (but no closer than the focal length plus a smidgen) to the optical centre of the lens, and the sensor will be more than twice the focal length away from the optical centre it sees. And just as the slightest nudge of the focus ring will mean the difference between focusing 15m (50 feet) away and focusing 6m (20 feet) away, with the distance between the sensor and lens hardly changing at all in "ordinary" photography, getting just that much closer to the subject means increasing the distance between the sensor and the lens by a huge amount (relative to the focal length) in micro photography. For this purely practical reason, shorter focal length lenses are used to produce higher magifications. The loss of working distance is more than adequately made up for by the reduction in size (and awkwardness) of the apparatus. It is one thing (and a difficult thing at that) to manage a 12.5mm micro lens on the end of a 30cm-long (12") camera + bellows arrangement to get a particular magnification of a very small subject; it is quite another to manage a 600mm lens on the end of a 14.4-metre-long (48 feet, roughly) camera setup for the same magnification.
For actual photomicrosopy, the sort of microscope eyepiece adapters that have been mentioned in other answers are The One True Answer. They allow you to use very short focal length lenses that have been optimised for high magnifications, as well as a rigid apparatus that will present the fewest number of problems in capturing an image.
If all you want is macro (that is, images where something roughly an inch or 2.5cm in size will completely fill an APS-C- or DX-sized frame), then you want a macro lens. The focal length will determine how far from the subject you need to be to get that 1:1 magnification (though since most modern macro lenses are internal focus designs, you would actually have to check the minimum focus distance and the physical length of the lens to determine what the actual working distance would be). You can achieve somewhat greater magnifications by using extension tubes in conjunction with a macro lens.
For more extreme magnifications without actually resorting to a microscope, you might find that reversing a wide-angle lens will get you where you want to go. Wide angle lenses (these days) are almost all retrofocal, meaning that they have a longer focal length on the "sensor" side of the lens than they do on the "object" side. When you reverse the lens, that means that there is a greater distance between the subject and the lens than the marked focal length would indicate, and that you can project a much larger image onto the sensor by moving the lens only a little bit further from the sensor (using extension tubes or bellows). Even mounting the reversed lens directly onto the camera body will (usually) produce images greater than life-sized.
¹ That is, distances at which no exposure compensation would have been needed to account for "bellows draw" on a unit-focus lens.
² I am simplifying reality here somewhat and using the thin lens version of the truth. With real photographic lenses, especially SLR lenses, it would be normal for the subject and the sensor to "see" the optical centre of the lens as being in two different places. The closest you'll see in the real modern world to true thin lens behaviour on SLRs will be with Tessar and Symmar lens designs. As well, internal focus lenses actually focus closer by "zooming out" to a shorter focal length while keeping the front of the lens anchored in space, so a "60mm macro" would actually be a 40-45mm lens when focused for 1:1.