Documenting a problem with User DSN and System DSN having the same name

Documenting a problem with User DSN and System DSN having the same name

Post by Dan Konigs » Wed, 14 Jul 2010 05:03:24


I've just encountered a subtle problem while using the Microsoft Jet
ODBC driver (odbcjt32.dll 4.00.6304.00 and 4.00.6305.00) for a
Microsoft Access database, and am passing it along for whomever else
it might help. I do not know if this problem is specific to Jet or
applies to other ODBC drivers.

If you create or modify a System DSN and there is already a User DSN
with the same name, then the configuration for the System DSN is
incomplete, with numerous registry entries missing. If you are logged
in to a user with the User DSN, then everything works, because the
User DSN is complete, and the ODBC driver looks in the registry under
both HKLM, where the System DSN is configured, and also under HKCU,
where the User DSN is configured. But, if you log in as a different
user without the User DSN or run as a service, they ODBC only sees the
incomplete System DSN.

Symptoms:
* In our case, the service got an error on SQLSetConnectAttr when the
Jet driver looked for the DriverID in the registry, and didn't find
it.
* Try this: Create a Microsoft Access User DSN, then create a System
DSN with the same name. Delete the User DSN. Look at the System DSN
- there's no configured Access database anymore!
* Another possible symptom: There was a posting of someone who had
both a User DSN and System DSN with the same name, deleted the User
DSN, and now can't delete the System DSN. That might be because the
driver isn't robust enough to deal with the missing entries.

The solution is not to create a Jet (and possibly other DBs) ODBC User
DSN and System DSN with the same name.
 
 
 

Documenting a problem with User DSN and System DSN having the same name

Post by Kenneth A. » Thu, 15 Jul 2010 00:44:25


Do you know what? I think you're right!