Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Are the Windows 7 clients 64-bit? Is the business app 32-bit? –  jscott Nov 23 '11 at 18:40
    
Both are 32-bit. Is that a potential trap? –  uSlackr Nov 23 '11 at 18:52
2  
You're 32-on-32, not the same issue we had then, sorry. With 64-bit OS and 32-bit ODBC app, be aware of the following: support.microsoft.com/kb/942976 –  jscott Nov 23 '11 at 18:54
    
I stand corrected. There are 64-bit clients. I'll read this through and let you know. –  uSlackr Nov 23 '11 at 19:28
    
@jscott this looks promising. Can you add it as an answer so you get credit? –  uSlackr Nov 23 '11 at 20:30

2 Answers 2

up vote 1 down vote accepted
+100

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.

share|improve this answer
    
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 7 '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 7 '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 7 '12 at 21:15

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.