Connection Strings in General
Added 29 Jul 2008
To begin with, connection strings are simply string variables which contain database connection information. There's nothing magical about them so you can use all the string functions you're used to using to manipulate them if you need to. The only thing that makes them a connection string is that they are then passed to ADO which will interpret them and act accordingly. Since they're going to be passed to ADO, they need to be in a format ADO understands. Your standard connection string contains a number of arguments set equal to their associated values and seperated by semicolons. It's basic form is something like this:
argument1=value1; argument2=value2; argument3=value3;
Of those arguments, there are only four that ADO understands. The rest are
simply passed through to the provider. The four ADO recognizes are
Provider, File Name, Remote Provider and Remote Server. The last two are
only used with RDS and I won't be covering them.
File Name is used to point
to a UDL file which can be used to provide all the other parameters.
As such your connection string would just contain this one parameter
and would look something like this:
File Name=C:\path\filename.udl;
You can create a UDL file by simply creating a new empty text file and giving it the extension .udl. Once created, you can set and adjust it's properties by simply double-clicking it from Windows Explorer or right-clicking it and selecting properties.
For more on using UDL files to help you build your connection strings, check out these articles from 4guysfromrolla.com: A Nifty Way to Create Connection Strings and Creating Connection Strings in Windows 2000. This can be a really useful way to start building your own connection strings in a user friendly environment. Just be sure to take a look at the resulting file in a text editor.
This leaves us with only one argument: Provider. This is the important
one. It tells ADO what provider it should connect to in order to access the data
you want. It is often left off in which case it defaults to MSDASQL which is
Microsoft's OLE DB Provider for ODBC. There are a number of providers supplied
when you install MDAC, but I'll be focusing on MSDASQL, Microsoft.Jet.OLEDB.4.0,
and SQLOLEDB which are the three most commonly used in ASP.
For additional information about other providers and about MDAC, OLE DB, and ODBC in general, check out Microsoft's Universal Data Access Web Site. It's an invaluable site which contains downloads of the latest drivers and providers as well as links to the detailed documentation on MSDN.