Goldeneye (Wii) and it's PS3 counterpart are my favourites for both their solid campaigns and multiplayer. I was also quite fond of Quantum of Solace, Bloodstone and even 007 Legends. In particular, the multiplayer had been consistently enjoyable throughout Activision's run, with smaller dedicated player bases only enhancing the experience in my opinion.
Here's hoping IO Interactives reign will deliver some exceptional new games and breathe new life into the spy/espionage genre as a whole.
I did recently picked up the DS Goldeneye 2010 game and that was utter tripe. Quantum of Solace DS is a pretty odd game you play it with the DS on the side and you have to control Bond throughout many top down levels, it's okay but the controls are a bit of a pain at times.
Really want Bloodstone on DS, I've heard some positive things about that one.