I have a fairly old drum machine (a Roland R-5) that I would like to play around with, so I am looking to buy a MIDI to USB cable such as this. I don't really know much about how MIDI works, though. Is that cable all I need to be able to produce sound with it (as well as a DAW)?
Yes, that'll work.
When connecting this cable via USB, your computer will now have a MIDI interface, which provides MIDI In and MIDI Out channels to connect your drum machine to. In your DAW, you will be able to assign the USB MIDI device to certain tracks or virtual instruments.
Note though that this will only allow you to do the following:
In order to record the actual sounds from your drum machine, you'll need to connect its audio output, not its MIDI output to the computer via a normal audio cable – probably 1/4" jacks. A normal headphone connector from the drum machine's headphone output to your computer's line-in might also work, but this could introduce latency while recording.
You may want to buy an actual USB audio interface, which allows zero-latency recording and also offers MIDI ports, like from M-Audio, Focusrite, et cetera.