The macro is simple, and can be cheap. You don't need a fast lens - and you don't even need af. A decent manual will do. Olympus OM lenses are adaptable not only on oly/panasonic bodies but also canon. You can get good results without too much expenditure.
Even on my p&s - an fz28, I was killing AF simply because i was focusing by moving the camera back and forth anyway. Use a tripod if you can, but if you're running around, you can't - shoot in bursts and try to capture the object in focus as you rock to and fro.
For macros, you can
1 - use a regular lens with a close up filter/extension tubes
2 - use a macro lens
A regular lens can be used anytime - but, you're sacrificing on iq. Good achromatic filters are pricey. You're looking at $100 for a 67mm canon 500d "filter". ~60 for a raynox 250.
You can just get a old manual focus macro.
If you're 30 yards away and want to catch something 10 inches, you're doomed. 250mm isn't enough. Even 500 is pushing it.
If you're looking to shoot something smaller farther away with a larger sensor, you will probably not be satisfied.
Your first upgrade then should be figuring out how to get closer, or lure the subject to make 250mm-400mm a reasonable enough zoom.
I shot this critter at ~15 yards with a 70-300mm @ 300mm WITH a 1.7 teleconverter (tcon 17) on a 2x crop body - that's 1020mm effective.
here's a monkey at 20 yards, 600mm effective
If you go the slr route, I'd suggest used lenses (after you read positive reviews on them - you know, google....)
e.g. Here's what you may look at upgrading to when you're comfortable with spending large amounts of money on your slr, and weighing yourself down.http://www.keh.com/camera/Nikon-Autofoc ... 2397N?r=FEhttp://www.keh.com/camera/Canon-EOS-Fix ... 40360?r=FE
F4 300mm without completely breaking the bank.http://www.keh.com/camera/Canon-EOS-Fix ... 6011K?r=FE
Couple a good 300mm F4 with 1.4x teleconverter, and you'll be F5.6, 420mm, and still good in the IQ dept.
If you want 500mm, you can get it for a little less than the 150-500.http://www.keh.com/camera/Nikon-Autofoc ... 4361R?r=FE
just remember that with third party lenses, you CAN run into quality control trouble.
I don't want to suggest 500mm though. It really does require vastly different use from your p&s. You can "get away with" not using a tripod with 300mm if you brace yourself, 500 is just that much harder that you really need a pod - and if you're new to dslrs, you'll probably cheat, and try to do without - everyone does. But that can lead you with a bad taste. So you need a tripod, it's an off brand where you may have issues, costly - and it's dark @ F6.3.
Start here http://www.slrgear.com/reviews/index.php
select the lens you want to read up on, and look for the "blur index" chart. You want a lens sharp when zoomed in.
start a spreadsheet
camera tele macro otherlens cost
nikon d90 (800) + 70-300 vr (450) + 30-200mm mf macro(225) = 1475
nikon d5000 (625) + sigma 70-300 (160) + canon 250d filter(80) = 855 add a tcon17 to boost that zoom (510!) for $95 off amazon. beware of the weight on your lens though. The TCON will work best on the nikon IF (internal focus) lens, where it's less likely the weight will damage moving parts.
Decide what's important to you - lens speed (aperture), high iso, cost, size, image quality etc.
Use the spreadsheet and put several choices down. Then start eliminating based off of research cost/sharpness etc.
I ended up with an Olympus e620 as a result of that. The 2x crops is useful on tele and macro depth of field. I cannot suggest Olympus SLRs at this time since their future in SLR is unknown and bringing out doomsayers.
If you are accustomed to p+s and want small, consider micro fourthirds however. Panasonic's GF1, and the olympus pens can use either's lenses. 2x crop factor.
Panasonic is releasing a 100-300mm this fall.
Olympus pen will work with the existing 70-300mm from their slr lines (albeit with sluggish af)
Almost any manual focus lens can be adapted. So you have cheap available lenses if you don't mind manual focus. This gives you a lot of cheap macro and low light lenses.
Sorry for the ramble. Just be aware if you want good IQ, you will have to pay. Do your research and take your time selecting the lenses/accessories you'll desire.