See, I find myself watching the show and find myself impressed
with how good the effects are! The S2 episode "Time Squared" when the ship is over the vortex struggling to stay stable is a beautiful shot and to think it was done with very primitive, by today's standards, effects and little to no CGI is impressive to me.
The "hokiest" things to me are minor things that are just a product of the limitations of the time, like when the Enterprise is near a much smaller sized ship but both ships look pretty much the same size (For example, the Stargazer
isn't likely to be much larger than a Constitution-class vessel but when the two are near each other both ships look about the same size) or when the limitation of choice when comes to guest ships means that we see the same ship over and over again. Oh, this time it's a different color, or upside down or whatever.
But mostly I think the TNG effects are good, the problem is they're not on a material that can survive an upgrade to HD resolutions and still produce a very good looking product.