ODBC Connection Strings

While I indicated earlier that we prefer OLE DB, ODBC (MSDASQL) has been around for a long time and back when ASP was first released it was really your only option for ASP database connections. Since it is still so widely in use and is actually the default provider, I felt I'd be somewhat remiss if I didn't cover it as well.

There are two styles of ODBC connection string: those that utilize a DSN (Data Source Name) and those that do not (often called DSN-less connections). The parameters they use are identical, it's just a matter of where they are specified and stored.

DSN Connections

DSN connections store their connection information in the Windows Registry. Naturally it's not a good idea to have people poking around in the registry so in the Windows NT4 Control Panel you'll find an applet called "ODBC Data Sources" which provides a wizard like interface to collecting connection settings. In Windows 2000, it's been moved to the "Administrative Tools" Folder in the Control Panel and renamed "Data Sources (ODBC)", but it functions the same.

In a nutshell, it's only real functionality is to help you create a connection string, save these connection settings to the registry, and associate them with a unique DSN. If you're using DSNs for ASP, make sure they're System DSNs so that they're available for all users. Since these settings are all previously saved, the connection string becomes simplicity itself:

Provider=MSDASQL; DSN=data_source_name;

or since MSDASQL is the default, simply:

DSN=data_source_name;

If you need to, you can also specify username and password information, but the parameter names are slightly different then with OLE DB resulting in a fully qualified connection to a DSN that looks something like this:

Provider=MSDASQL; DSN=data_source_name; UID=username; PWD=password;

Any other parameters you need, including what driver to use and even what type of database to connect to, is set via the wizard-like setup in the DSN configuration utility in the control panel discussed earlier.

DSN-less Connections

A DSN-less connection is operationally identical to a DSN except that the server doesn't have to access the registry to access the parameters since they're all specified in the connection string itself.

The only parameter required by all ODBC connection strings is Driver which specifies which driver to use. Unfortunately this isn't enough to get connected to anything and additional information is required by each driver. Most have a similar syntax and I'll provide samples of the couple most popular ones below:

Microsoft Access

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

Microsoft Excel

Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\path\filename.xls;

Microsoft Text

Provider=MSDASQL; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\path\;

Notice there is no file name. The directory acts like a database with files working in the role of tables. Hence, you don't specify the file name until opening a recordset. It's a little weird... I recommend you experiment with it some first if you find a need to use this driver.

Microsoft SQL Server

The SQL Server driver again requires some additional information to get connected:

Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;

 

A few final notes about all the ODBC connections above. There is an interesting bug that I seem to remember causing problems for a while. If the above strings aren't working, try removing the space following the semi-colon after the Provider parameter. There used to be a bug in the OLE DB Provider for ODBC that would cause this to fail:

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

but this to work:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

And remember that MSDASQL is the default value for Provider so if you want to, you can simply leave the parameter off altogether. In fact, you'll notice that almost all the sample code you run across does leave it off. I've included it in the samples above for illustration and because the Provider is a relatively central point to get in order to really understand how all these parameters fit together to allow these connection strings to actually get you connected.