1

I'm working with a small business that uses an Access-based database for work order mgmt. The system has been around for years and they have 6-7 PCs using custom software from an ISV to access the database. The database is accessed over a mapped drive (Z:) connection.

Several months ago they began getting this error intermittently:

Data source name not found and no default driver specified

This results in the ISV having to connect to the database and run a repair in order to recover the database. The error they see is a bit more specific and suggests that the file format is corrupt. The support tech suggests that the issue is caused by a transaction failing across the network. To that end we've tried several things

  • moved the database to a different host in case the original "server" PC had issues
  • replaced the network switch
  • started taking the clients off the network one by one in an attempt to isolate the problem child with no consistent results

So far, no luck.

My question(s) - Could one of the PCs be closing its drive mapping and corrupting the open database - Is there anything new in Windows 7 that might be getting in the way - Can you recommend a better approach to isolating the cause.

uSlackr
  • 6,412
  • 21
  • 37

2 Answers2

1

This is almost certainly a 32-bit vs 64-bit DSN issue. To use a 32-bit DSN in a 64-bit environment, go to C:\Windows\SysWoW64\odbcad32.exe

Our internal application has a very similar limitation. To avoid the issue in the future, you might want to install the latest SQL Server Native Client and deploy both the 32-bit and 64-bit DSN on every machine via Group Policy.

Joel E Salas
  • 5,572
  • 16
  • 25
  • Thank, Joel. Q:Would this cause inconsistent results? These clients work fine and then for some unknown reason the database gets corrupted and the client fails with the error above. – uSlackr Feb 07 '12 at 12:14
  • Which SQL Server client are you using? Generally you'll want to use the latest, as they're fairly backwards-compatible. – Joel E Salas Feb 07 '12 at 17:13
  • This is an Access application. I'm not aware there is any SQL client installed. If there is then 3rd party app installed it. I'll need to look. – uSlackr Feb 07 '12 at 21:15
0

If you map the drive using the SUBST.exe command instead of "NET USE", then unlike "NET USE" the connection will always retry when the mapped drive is lost. Keep in mind that doing it this way makes it difficult to unmap the drive for person who don't know about the SUBST.exe command. When a drive is mapped this way you can't just disconnect it from windows exploder... that wont work.

Personally, I agree that it is the 64-bit issue.

Keep in mind that the 32-bit and 64-bit ODBC DSN control panels, while you would expect them to work a certain way, in some cases they do the opposite. For example: when on a 64-bit system and trying to add a 64-bit "User DSN" you might notice that your connection fails but using a "System DSN" it works. This is because the ODBC panel is actually generating a "32-bit DSN" in the "User" tab of the 64-bit ODBC control panel while it will generate the expected 64-bit DSN in the "System" tab. As long as you are aware of the possibility that the control panels dont do as expected, I don't think any configuration will stump you.

djangofan
  • 4,182
  • 10
  • 46
  • 59