It first appeared in SFC version of Akumajô Dracula, a.k.a. Super Castlevania IV. Since then, it's been used in the Sharp X68000 version of AD (which was rereleased as Castlevania Chronicles) and Vampire Killer (Castlevania Bloodlines). It's strange, really, that one of the most iconic tracks in the series has appeared in so few installments, but that's probably because weight is being lent to its title such that it's only used when Simon's on the job, which doesn't happen too often anymore. I guess Konami decided enough was enough with four remakes of his first adventure--five if you count Chronicles, which was a remix of the last remake.

Yes, it's true that the theme is used in Vampire Killer even though Simon wasn't in that one, but I think that's just because, game-wise, VK was supposed to be the SEGA crowd's answer to the Simon-centric Akumajô Dracula.