Lambert Smith has a great howto for converting a standard 50mm lens from an old SLR camera into a dedicated macro lens for your digital camera. By reversing the 50mm lens and using a number of extension tubes. The photo above is his conversion hack on a Canon Powershot G3, which has a non-interchangeable zoom lens. Custom adapters can be made by gluing filter rings back to back, so you can do this with a normal digital, a DSLR, or even a traditional film camera.
Once set up in this manner, your camera will have a fixed point of focus (unless you use a bellows). When taking a photo, you simply move the whole camera toward or away from the subject until it is in focus.