Yea, your best bet would be to upgrade your motherboard and memory to go with the ATI card. You might want to stick with a 4x AGP board as I hear the 9700 has 8x problems on some boards. Do your homework first by visiting some other hardware related forums to make sure you don't run into any incompatabilities. There are some other ATI users here that can give you some recomendations of what works best for them.

As for your Hard Drives, you didn't mention if they were ATA 33, 66, 133, or SATA. Most motherboards support the 33, 66, and 133 with a growing number of them also supporting SATA. I don't see any reason why you shouldn't be ok using what you have. Like I said, just do your homework and you'll be fine.

EDIT: One other thing, you didn't mention what model Compaq you have so I'm gonna recommend that you get a new case because it's POSSIBLE you wont be able to drop a new motherboard in the Compaq case.