Absolutely it will help. Going from a dual-core, especially an older one, to a quad-core is one of the better improvements you can make. That motherboard will support some pretty decent CPUs, like the 965 Black Edition, which can be had used for about $65 and is not top-end but still enough to play about any game out there reasonably.
Of course, without a decent graphics card to match, you will only improve by so much. So make sure you're at least somewhat balanced there.
DDR2 is not really a limitation, so much as the amount of DDR2 you have. If 4GB or less, you may bottleneck yourself. And since 32-bit Windows only supports 4GB, you may be at a dead-end if that's the operating system you have.
I would say go for it, as long as you can support enough RAM. If you spend a lot of money on a video card, so what; you can carry that over into your next system. But if you have to upgrade the CPU, video card, RAM, and a new copy of Windows, then you're already most of the way to a new system, so just do that instead.