I started watching it yesterday but unfortunately it is a really poor camcorder job. I could not bring myself to spoil it by watching it in such low quality, I will either wait untill a decent camcorder version comes on the net, or wait untill the cinema release.
As for the person dying (spoilers aside) I assume you mean the reference in All Good Things (The last TNG episode). This was only one possible future, but it is entirely possible that it is this person, but I hope not as I really like the character.
As for killing off major character DS9 was king. It is my favorite of all the Trek series'. When you compare the death of Jadzia with the death of Tasha Yar, there really is no comparison. You are right about TNG's lack of ambition too. I am really getting into Enterprise now too. It was a bit slow to start, but the early episodes of season 2 have been great. I thoroughly enjoyed Carbon Creek, and the whole concept of the temporal cold war is great too. I see the series turning out quite dark, the two parter Shockwave being a real sign of things to come. As far as first seasons go, it is pretty damn good.
As for insurrection's special effects. The move to doing the space scenes completely CGI was a real bad move. The CGI-textured-models in First Contact looked real, and correctly proportioned, the fully-CGI ships in Insurrection looked really poor. You say the enterprise looked bad, by comparison to the alien ships it looked good, and that's in no way a compliment. Hopefully they will have returned to the half-and-half look of First Contact for Nemesis. However, I don't care how poor the FX were, for me Levar Burton's acting as Geordi was the saving grace of this film. The emotion he showed when he saw that sunset for the first time was stunning - that guy can really act.
Now I need to wait 'till January to see the film.