vrenifossi wrote:
On my WinXP PC the drive would not show up on explorer, but it is present with a letter in the drive manager (which does not indicate any problem).
Not surprising for that to show no problem, as it does almost no I/O i.e. no "test" of the drive - however to show as a drive letter in Windows Explorer, more of the filesystem metadata (FAT or NTFS) must be read. That filesystem access seems to be (sometimes) unsuccessful on that drive, based on your description.
vrenifossi wrote:
I get told that I need to format the drive first before I use it.
That just means, again, that the filesystem metadata could not be successfully read, so Windows believes that there is no filesystem and hence it tries to be "helpful" and suggest that you format it, in order to write a filesystem. Of course,
do NOT do that!vrenifossi wrote:
my old one has one continuous two row pin plug [...] I calculated that the device must be some 6+ years old
Therefore this all sounds like it is an IDE (Parallel ATA) interface - if you included a photo of the interface connector on the drive, then this would allow confirmation, but your description fits with it being that type of interface.
vrenifossi wrote:
My guess is that there is something wrong with the hardware in the housing
I politely disagree. While I cannot be sure from 1000s of miles away

the behaviour you describe is completely consistent with a fault in the drive itself (failing heads, internal mechanical issues etc. etc), as well as the very small (nearly zero IMHO) possibility of what you guess i.e. an intermittent problem with that USB-to-PATA housing.
However, it would be very, very rare for a problem with the housing to allow a drive to seen in Windows Disk Manager, and not in Windows Explorer, because the housing itself does not behave differently for these 2 different cases. That only leaves the cause of the different behaviour to be the drive itself (i.e. some sectors are unreadable).
Therefore if you plan to go ahead with a DIY recovery attempt, you can try to get either a PCI Parallel ATA (IDE) interface card for a desktop PC (better for data recovery), or try another USB-to-PATA (IDE) adapter for a laptop (not so good for data recovery), in order to confirm that the original housing is not the cause of the problem. These types of interface are, as you say, old. (Have you tried ebay or your local equivalent?)
If (as I expect) the existing USB drive housing is OK, then you can try to use that with cloning software (search the forum for lots of suggestions for suitable software in the past), but you have less chance for success when using a USB adapter, as this adds extra (unwanted) error recovery into the process. You might be lucky...
Then you can attempt to clone this drive, ideally to another empty disk (which you need to get - 80GB or larger; it can be SATA if that is easier for you, and can be in a USB housing) and to use cloning software which will skip bad (unreadable) sectors to attempt to clone the readable sectors from your old IDE disk, onto the new (empty) disk. As I said, search this forum for "cloning software" to see many recommendations in the past - I won't waste time repeating them again here. After that, then you are likely to need to use data recovery software to try to recover what files are possible from the (partial) drive image on your clone.
There are risks of you doing DIY (again lots of info already on the forum about this) including you making an error with the cloning software and overwriting your drive; you damaging the drive with ESD or mechanical shock; your new interface card/adapter damaging the drive; the drive dying during your data recovery attempt etc. etc. but it seems to me that if (as you said) you want to try this, then read about cloning software. It would be a good idea for you to consider practicing cloning another disk, before trying your "real" problem disk.
I hope that gives you some ideas. However I don't have time to "hold your hand" through the process.
Good luck...