I've got a little collection of old Nikon glass to use on my D5000. If you can handle manual focus they are well worth a try. It its a bit harder as SLRS do have dimmer and smaller viewfinders but you can easily review focus on the LCD (live view can work like a big focusing screen). Also you will typically have to work in full manual exposure, but again not a big problem on digital where you can immediately review your exposure and correct as needed.
I would not recommend for sports or fast action for the most part, where auto focus and exposure is very handy. Great for landscape, portrait and macro.
Avoid older non-AI lenses as they will not mount and could damage the camera. Everything AI and later is cool. Ken Rockwell's site has a good compatibility guide which also makes it easy to tell non-AI lenses from ready to spot physical characteristics.
I do quite a bit of aquarium photography. It can be challenging, with small moving objects in weak lighting. I often use manual focus as auto gets thrown off by the glass, plants, rocks and other fish. I recently bought a 40 year old 55mm micro-Nikkor lens. One quarter the cost of a new equivalent (I had given up hoping to afford a macro lends) and from the first time I put it on my shots have improved immensely. Beautiful bit of gear to use - smooth and sharp.