Judgment Rites, the sequel to 25th Anniversary, is good. A Final Unity is also good. One word of warning, though, is that some late-game puzzles in both of those are absolute bullshit.
Armada was a good RTS, I didn't much care for Armada II. (Though the Armada III mod for Sins of a Solar Empire is excellent.)
Birth of the Federation was almost a good 4X game, but crippled by janky code and a game-breaking memory leak that renders the game almost unplayable after Turn 200.
Bridge Commander was decent, not great. Helped a lot by mods.
Starfleet Academy felt like Wing Commander with a Star Trek skin, especially with ship movement. Klingon Academy was fun, but janky and held together with toothpicks, chewing gum and the wails of the wretched and the damned. You're better off watching the cinematics on YouTube.
The three Starfleet Command games were good, though the first two were the best.
Stay far, far away from Star Trek: Legacy, Hidden Evil, New Worlds and D-A-C. And if you see a copy of the Star Trek game from 2013 (the one that's based in the Kelvin timeline), set it on fire, because it's unplayable.