You can try to uninstall the drive from Device Manager, reboot and see if that will solve the problem.
Type 'Device Manager' into Start>Search then right-click your drive and select uninstall. Then reboot:
Try scanning for hardware changes using Device Manager. There is a registry hack for this problem that you may want to try as well: Restore CD/DVD Drives to Explorer
It's not totally dead if it works occasionally though....
The next thing I would be tempted to try is to open the case and physically disconnect the drive from the motherboard. Check the IDE and electrical connection of the drive too; they could be loose or not seated properly. Reboot. Then shut down the PC, connect the drive and reboot and see if Vista 'finds' the drive.
Failing that, I'm not sure what to try next. It could be a failing drive, in which case a new optical drive would be the answer.
These things happen; I had it occur once myself. The only thing else to try is to rule out a defective IDE cable, but I doubt that is it. But, its better to spend a few bucks on a cable than big $$ on a new optical drive though.