DSLR cameras are basically what the professionals use. They are a bit more expensive than most point and shoot cameras, but there are some that are pretty affordable.
I've seen some great pics taken with some of the nicer point and shoot cameras, but personally if you want to take some great pictures (not just aquaria related) then it's worth it to invest a little more and get a DSLR.
Here's a nice writeup on the specifics.
Should you buy a DSLR or Point and Shoot Digital Camera?
I got lucky because my girlfriend is a photographer, so I have access to a nikon d90. Before that I only used a few different kind of point and shoot cameras, and I know exactly what you mean about it being difficult. I used to take 20-30 pics and maybe keep 1 if I was lucky. The difference is night and day.
With the DSLR, I use the digital display to zoom up really close on the fish and then manually adjust the focus so that most pics turn out fairly well if the fish is cooperating.