I just wanted to report back about the true cause of the issues with large drives vs Adaptec drivers.
After narrowing everything down, I determined that the ASPIDISK.SYS is not the problem at all. In fact I can use the exact same version of ASPIDISK.SYS on a machine with a different Adaptec controller (using ASPI2DOS.SYS) and it handles the large drives with extended translation just fine.
The real problem is a bug in ASPI4DOS v3.35 (1997). I have a AHA-1542B with two drives connected, a 2GB and a 74GB (with a 2GB FAT16 partition for DOS access). The 3.20 BIOS is installed and enabled. If I simply boot into DOS using the BIOS and do NOT load any SCSI drivers, both drives work. If I load ASPI4DOS.SYS in my CONFIG.SYS (just the ASPI manager, nothing else, no disk, no CD) then the DIR command to my larger drive will fail as described above, but access to the 2GB drive will continue to work as normal, even though both drives use extended translation. So from that I can assume the bug in the ASPI4DOS manager driver is specific to large HDDs, though I cannot determine an exact size threshold.
It's possible this bug is fixed in the final version of ASPI4DOS, but we will never know since apparently no one can get it to work with a 1542B.