Current PC specs: 3200+ 64 bit, 1GB PC3200, Geforce 6600GT AGP, xp
sp2. Playing Battlefield 2 is not a good experience as you can imagine
(I can play at 1024x768 but at low avg frame rates), and when Unreal
Tournament 3 comes out in Q3/4 I will need to upgrade anyway. I would
like to play BF2 and UT3 at reasonable resolution/framerate without
replacing my entire computer. Here is my plan..

Replace 6600GT with eVGA 256-A8-N506-AX Geforce 7800GS 256MB 256-bit
GDDR3 AGP 4X/8X.
Add 1GB PC3200.

Will this give me what I want?

With such a slow cpu, should I look into a passive cooled 7600? I feel
a 7800 may be better paired with a faster cpu (according to a few
benchmarks at least); thanks for your help.

that should give you decent performances, but keep in mind i believe that BF2 and Unreal 3 will have a huge difference in performance and such.

and also, a 7800GS AGP4x/8x will be slightly weaker than a 7800GS PCI-E.

but unless you plan to max out the resolutions and effects, they should be able to run pretty decently.