if it helps, i shoot in S mode. sometimes M mode, but mostly S since it works for me. i throw an extra light on top of the tank, make sure you have LOTS of light. set the ISO low to about 200. the D40 does not take good pics at high ISOs. they get really grainy at anything over 800. i then set the shutter speed to about 1/40 to 1/100, depending on how close to the light the fish is.
I love D40...the only feature that it lacks for me is the ability to auto focus with the 50mm prime lens. Its good practice on manual focusing but its a pain in the butt trying to track a moving object like a cat. That being said, I've still been using the lens almost exclusively since September.
The lens is really the trick. The higher quality lens you use, the easier it will be to take killer pics. Few years ago I traveled around the country shooting drag races. Used Canon 20d's and Canon "L" series 70-200mm lenses. A blind man could take good pictures with that lens.