First, take a look at this pending UHH Frequently Asked Question:
http://www.uglyhedgehog.com/user_page.jsp?upnum=108

I use a Nikkor 105-mm macro lens on my Nikon D90, and can acheive 1:1 magnification (life-size) at 150-mm (6-inches) Working Distance (between lens front element to subject). For a DX size sensor, WD is roughly 1.5x macro lens length, so a 40-mm macro lens has a WD of approx. 60-mm (2.5-inches).

Adding extension tubes to ANY lens will shorten Working Distance, sometimes to the point of working in your own shadow (too close) or spooking insect subjects (too close).

There is no image quality difference in whatever extension tube brand you get, as there is no glass in an extension tube. It only extends the lens farther away from the sensor giving you closer focusing, and thus increasing your image closer to life size.

To figure how close you are to life size using extension tubes, the length of the extension tube equals the milimeter of the lens at 1:1 life size. If you use a 50mm lens, 50mm of extension tube will give you life size with film. I haven't kept up on it enough to know the lengths needed for life size considering the camera's sensor multiplying factor.

Thanks for the wealth of information. This has saved me a few hours of grief... thanks again.
One thing to be very aware of if you have "G" series lenses without external aperture rings is that manual focus extension tubes will not work. Your lens is not electronically connected to the camera body so you loose aperture adjustment. You need auto focus extension tubes with "G" series lenses. With any lens that has an external aperture ring then you can use manual focus tubes.

You will be using manual focus anyway with whichever extension tube you get (auto focus, or manual focus), but you keep your elecronic connection with auto focus, for things like using your internal aperture, or using your focus dot to let you know when you are in focus. The focusing, even with spot focus, will not always focus on what you want, use manual focus.

You will have your best macro experience using the 24mm-70mm lens because you will be closer to, and over 1:1 life size with a regular set of extension tubes.

Another thing to consider, and it is only manual focus, meaning you have no electronics, is a bellows system. These give you variable extension without having to change tubes, you just slide the bellows in, or out to fit your need. With a bellows though everything is manual.

Another thing to consider, and it is the cheapest, getting you over 1:1 life size, is a reversing ring. It esentially turns the lens around on your camera, reversing it. They are completely manual, but are very cheap, with no quality loss as they are not extra glass. You will need lighting with a reversing ring because you are very close with the lens casting shadow. I use a flashlight to light with a reversing ring.

Ebay is good for all these cheap macro attachments. What you loose is the same things your lens is deficient in, but magnified. The big thing is edge sharpness, and light fall off in the corners.

With a full set of extension tubes on your 70mm-300mm lens you will get about 1:2 life size, but with your 24mm-70mm you can get close to 2:1 life size. A good set with auto focus runs about $100US, with a manual set running about $40, then a bellows attachment will run about $50.

Something else you will want to get, and this goes for however you decide to enter macro, even with a full life size macro lens like the Tamron 90mm, is a focusing rail system for the bottom of your camera connected to the tripod. Focusing is so so critical shooting life size macro a difference of a few millimeters can make all the difference, and it is real hard to move a tripod around these miniscule distances. A focusing rail system has knobs so you can adjust the small distances by only twisting the knobs. They cost about $50.

Have fun.