My favorite Star Trek game is Star Trek Aramada 2. Although you mentioned the original Armada, Armada 2 deserves special attention. I've played it countless times, even today. There is just something about zipping around an RTS map at warp speed that never seems to get old. That, combined with a good story, cool ships/races/weapons, and so on, make it pretty great. The borg and species 8472 races work quite differently from the others, so you actually get some variety. Also, it has a different feel to it than most of the other RTS games, (C&C, Warhammer Dawn of War, Empire Earth, etc. etc.) which all feel like they are just remakes of the same thing over and over.
I was so sad about Legacy, which is (to me) the "spiritual successor" to Armada 2. They are both from mad doc. Legacy, like Ben says, had a big scope, and featured voice acting from all the captains (I can't remember now if Sisko is in it or not, I don't think he is). Anyway, it was so obviously a bad console port, and what killed it for me was the control system, which just did not work well with a keyboard and mouse. I'll just have to wait for Armada 3 (yea, right- maybe there will be a Homeworld 3).
Oh yea, Second take is great! I look forward to it each week.