Klingon Academy is my favorite Star Trek game, and definitely in my favorite top 10 games of all time. Though it doesn't give you the "full" Star Trek experience (i.e. like an adventure game might), the space combat was second to none. I played the game regularly from 2000 - 2003, but sadly I've never been able to get it working for more than 10 - 15 minutes at a time since upgrading to XP.
KA was my first foray into modding. I remember Klingonacademy.net (I think) where I downloaded Galaxies, Sovereigns, Borg Cubes, and many others to spice up the 8 on 8 battles. Taking out 8 fed ships against 1 borg cube was always a blast.
My personal favorite ship was the Emperor class Klingon ship, I think it was the equivalent to the Fed's Excelsior. I don't know why, but I felt like that ship had the perfect combo of speed, shielding, and firepower.
All the cutscenes really sold the game, and it really had a very nice Klingon-centric story that -- while different from anything in Trek I'd seen to date -- felt very right within the Trek Universe.