DDR400 is not crawling. In a side-by-side test using a Conroe chip, a dual-memory motherboard, shows that the DDR400 holds its own with DDR2 800. The major difference is in overclockability, which isn't an issue for most folks. There are other differences too, but the performance isn't so far off.
The Socket478 is a pretty slow interface. Even if the chip is 3.0GHz, the FSB is only 533MHz. I had a P4 2.6GHz running at 533MHz FSB and it was a big bottleneck for AH until I upgraded to a Conroe chip.
The 6800 GS *should* be able to run AH okay with slightly reduced settings. Medium-quality visuals, but it'll run. A 7600GT will blow away a 6800 GS, but if you're in AGP they're hard to find and much more expensive than PCIe counterparts.
I hear they're releasing the ATI HD3850 in AGP. I don't know if it's out yet (I think it might be), but this would be the top dog end-all of AGP cards. However, your CPU really wouldn't be able to handle it.
478 is a dead end. Your RAM proabably is DDR233 or at most DDR333, not even DDR400. You probably have an AGP4x port, not an 8x. My sister has a socket478 I tried to upgrade, and the CPUs don't go much higher than what you already have. The video card choices are limited unless you want to spend a lot of money, and the RAM was picky, slower, older, and it was limited to 1GB max on the board.
Then you run into problems of the power supply being inadequate, if you DO upgrade.
My advice is to save up. Just save up. Put money from your checking into your savings account. When you have enough, get a newer system. Get something that can be upgraded in the future! That way, even if you get a medium-speed system, you can get faster parts when you can afford them later.