Personally, I think its best to use a digital camera - simply because you can take so many and then weed out the bad ones without having to pay for them to be developed.
I take pics at night. I turn all the room lights off, except for the tank and taht generally works.
Flash can be good for taking closeups of fish (but only if you take it from an angle to avoid the glare).
Also, make sure to clean your glass first. I've been so mad when I've taken a great pic and it got ruined by drip marks or algae spots.
Make sure the setting is on closeup (obviously). And, another thing that can help a lot is a tripod. It reduces bluriness from your hands shaking ever so slightly. Plus, a good camera helps a lot. I used my moms for awhile and it stunk.