I would highly recommend the V6 in the new Accord. The new V6/Auto combo has come a long way.
I recently purchased an '05 Coupe V6 6MT.
The new V6 engine is miles ahead of the previous generation.
The automatic I test drove was extremely nice, the tranny feeling much much better than the previous generation tranny. The previous generation V6/Auto combo sucks horribly IMO.
The V6 6MT has about as much power as you would want in a front wheel drive. It's got a little torque steer as it is, much more power and you are definitely going to want RWD or AWD. This car will beat a lot of V8's (5.0 Mustangs, Camaros, etc..) and still gets 30mpg on the highway.
Also, there is no requirement for premium fuel. It clearly states 87 octane or better in the manual. However there are reports of V6's dynoing and calculating 260hp at the crankshaft with premium fuel. (due to a special variable intake or something on the 6MT model) I definitely have to say I can tell a difference using premium fuel.
Also, as far as gas mileage, I haven't gotten better than 28mpg, even all highway miles. I do drive fast though.
Cliff notes: Get a V6