Specify a default driver for ODBC

9
votes

I have a legacy VB6 app which builds a DSN based on a parameter in a config file. The parameter is an ODBC connection, and the connection has a name (DSN-NAME) which maps a server (DBSERVER) to a driver ("SQL Server Native Client").

Generally, it builds a DSN like this:

DSN=DSN-NAME;User=foo;Password=bar

If I specify a hostname in the file, it builds a connection string which says

DSN=DBSERVER;User=foo;Password=bar

The error message reported is:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This suggests to me that there is perhaps a way of specifying a default driver, which may mean I can specify just the server name in the config file, and not need to create the ODBC connection.

(I am aware these can be created automatically; this is just to simply installation, and to satisfy my curiosity).

How do you specify a default driver? If I can set the default driver to SQL Server Native Client, can I then say DSN=DBSERVER and connect?

Edit: the point was to try and do this without changing the connection string. All the research suggested this isn't actually possible, but he wording of the dialog suggested it might be.

4
vb6odbcadodbdsn

4 Answers

4
votes

I had the same problem and fixed it by using the 32-bit ODBC admin to create a 32-bit DSN rather than the 64-bit admin in Administrative Tools which only creates 64-bit DSN which don't work.

The 32-bit ODBC Manager is located at C:\Windows\SysWOW64\odbcad32.exe

See this article "Data source name not found and no default driver specified" on Corey Gilmore's blog.

1
votes

To specify a default driver, use DRIVER= in the connection string:

DRIVER=driver name here;DATABASE=mydb;USER=foo;PASSWORD=bar

The driver name is the name that appears in the Control Panel ODBC config tool for each driver. Note you will need to supply the info which would normally come from the DSN, in this case the database name.

1
votes

You can achieve what you want by appending ";SERVER=dbserver" to your connection string.

There is already a server specified in your DSN, but the SERVER keyword in your connectionstring will override that.

http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx

0
votes

Use a DSN-less connection string...it can be created 'on the fly' to exactly suite your purposes....no piddling with odbcad32.cpl or reg/ini files to control/configure a dsn required.

see http://support.microsoft.com/kb/147875

for details