(okay, I have to interrupt myself to tell you that Scratchy has crawled up UNDER the slipcover of the loveseat - where I'm typing right now - and is tunnelling along beside my arm, a sage green ghost with claws.)
Anyway. Here's what I'm using. Nothing very fancy, really, as far as cameras and lenses go. I've got a Canon Rebel XT that I've had for...um...two years now? Of course, now there's the Canon Rebel XTi and the XSi...there's always a newer version. But anyway, that's what my camera is, and I have the kit lens that came with it - an 18-55mm f3.5-5.6 lens. See? Nothing fancy. I've also got a Quantaray 55-200mm f4.5-5.6 zoom lens that I use most often when we go to the zoo, so I can get as up close and personal to the elephants as possible. I love the elephants.
But for the close-up stuff - I bought a set of Opteka close-up lenses - they attach to my kit lens. I had originally wanted to buy a separate "real" macro lens, but it just didn't fit into my budget at the time. So I bought this inexpensive set, and I'm pretty happy with it. The set includes a 10x macro lens, and that's the one I use most often for up-close-and-personal shots of food or bugs or dewdrops or whatever. It's fun.
Eventually, some day, I'd like to get the Canon EF 50mm f1.4 lens, which will allow me to take pictures in lower light without using a flash than I currently can. (Which will be nice for when I'm taking pictures of something I made for dinner, for instance, and the natural light near a window is waning, and all I've got are the stove light, the overhead light, and/or the flash. Ugh.)
That's about it for equipment. I should use a tripod for the close-up stuff, too, but I don't. I just plunk my elbows down on the counter, inhale slowly, and snap the shot on the exhale. Not particularly good picture-taking advice, but I'm not a professional anyway.
The little close-up lens set is a great investment - a lot of fun macro bang for relatively little buck. If you are so inclined, you should get the set and start playing. It becomes addictive.