Rebooting the PC showed that the drive was visible from the BIOS and I was successfully able to boot from it. Also the drive was working fine from my Linux partition, so I was convinced there was no hardware problem. Why on earth was Vista having a problem with it?
A brief web search revealed I wasn't alone in this problem and that it's been there since XP and hasn't been properly fixed. The suggestion repeated over and over was to delete a few registry keys and reboot. I won't repeat the exact details here as it's well documented elsewhere, but if you're interested, see this Microsoft support page.
However, this fix didn't work for me. I tried uninstalling the drive in Device Manager and letting it try to redetect it - it didn't. I tried scanning for new hardware, updating the motherboard drivers for my PC (turns out I'd missed one of Nvidia's rare Nforce driver updates back in March). All the usual stuff you would do to fix this kind of problem, but didn't get anywhere.
Eventually I stumbled upon a fix on the web which involved deleting INFCACHE.1 from C:\windows\inf and rebooting, letting Windows rebuild the file. You'll need to set the security properties for the file so you have full control to delete it, but it did the trick. One reboot later and the DVD drive was back in working order.