Anybody who's interacted with foreign data programmatically knows that getting the connection code right can be frustrating. If you support yourself, you might find a Data Source Name (DSN) easier to create than a traditional connection string. Susan Sales Harkins shows how it could work for you.
Anybody who's interacted with foreign data programmatically knows that getting the connection code right can be frustrating. If you're lucky, someone in your organization can provide the correct connection string. On the other hand, a lot of us are our only technical support. If you support yourself, you might find a Data Source Name (DSN) easier to create than a traditional connection string.
I'm not suggesting that administrators move all their connections to DSN, so don't light those torches just yet. A developer of any worth at all has no more trouble creating a connection string, but this sensible DSN solution is for the lone wolves who don't have the technical resources some of us take for granted. However, even developers will find an existing DSN faster to implement. (Did someone just light a match?)
This article considers only Microsoft Windows technology.
This blog post is also available in the PDF format in a TechRepublic Download.
About connection strings
A connection string is like a combination lock. You must know all the numbers and their sequence to spring the lock. The connection string contains all the information the provider needs to establish a connection to the data source file. Most providers have multiple possibilities.
The provider needs the address to the server or the path to the data file. The string passes this information so the provider can use the appropriate instructions to gain access to the data source.
A connection string consists of several keyword=value components separated by the semicolon character, in the form:
strCnn = key1=value1;key2=value2;key3=value;...
Providers aren't consistent, and it's up to you to know the keywords and the acceptable values. For instance, the following opens an ADODB connection to a local copy of Northwind (the sample database that comes with Microsoft Access):
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Program FilesMicrosoft Office\" & _
"OfficeSamplesNorthwind.mdb;" & _"Jet OLEDB:System Database=MyWorkgroup.mdw", "username", "password"
Alternatively, you might prefer the following simpler syntax:
Dim cnn As ADODB.Connection
Set cnn=new ADODB.Connection
Developers eat connection strings for breakfast, but if you don't live and breathe this stuff, you run the risk of using the wrong values or forgetting a required keyword. Just remember that there's nothing wrong with connection strings. In fact, they seem to be the connection method of choice. A good source for connection strings is at connectionstrings.com.
The DSN alternative
Technically speaking, a DSN is an identifier to a data source for an Open Data Base Connectivity (ODBC) driver (library). ODBC is a function library that interfaces between the Application Programming Interface (API) and compliant database systems. This identifier includes the following information:
- Database name
- Database driver
- User ID (optional)
- Password (optional)
Microsoft Windows usually stores a DSN in the Registry; UNIX stores the configuration in the odbc.ini file.
Simply put, a DSN is a shortcut to and a way into a data source. (Although you can use a DSN with the Web, OLE DB is the preferred method for working with database connections via the Web.) To create a DSN (using Windows XP) follow these steps:
- From the Windows Start menu, choose Control Panel.
- Double-click Administrative Tools. If you're using Windows XP's default theme, click Performance and Maintenance, and then click Administrative Tools.
- Double-click Data Sources (ODBC).
To create a system connection that's available to all users, click the System DSN tab. The difference between a File DSN and a System DSN only seems subtle. Anyone who has access to the file can use a File DSN connection. Any person on the system can access the file via a System DSN.
- Click Add, highlight the appropriate driver, as shown in Figure A. Then, click Finish.
- Enter a name for the new DSN. It should be succinct, but meaningful.
- Enter a more complete description for the connection, as shown in Figure B.
- Click Select and locate the data source file to which you want to connect. Figure C shows a connection to Northwind, the sample database that comes with Access.
- Click OK and Windows will add the new DSN to the System Data Sources list, as shown in Figure D.
- Close the Control Panel.
Select the data source file's driver.
Give the DSN a meaningful name.
Locate the data source.
Windows adds the DSN.
When opening a connection, replace the connection string with the name you gave the DSN (step 6). For instance, the following code opens a connection to a local copy of Northwind.mdb using an ADODB Connection object:
Set conn = Server.CreateObject("ADODB.Connection") conn.Open = "NorthwindDSN"
The name of a DSN is much easier to remember than all the keywords and possible values that compose a connection string.
When not to use a DSN
Whether you're a developer or just trying to get your work done, a DSN is easier to use.
That doesn't necessarily mean it's the best choice. Some developers claim that a DSN performs slower than a DSN-less connection. The general thought is that numerous concurrent users mean just as many hits to the Registry, which slows things down. For most of us, performance probably isn't much of an issue. If you're in doubt, try it out and see what happens.
The more likely issue is maintenance. You must register a DSN on every user's machine — well, any user who wants to connect via that DSN. If you're working on a single system or you're the only one using the DSN, this isn't a problem. However, if you're working with several users over a network with several systems, updating the DSN might become more of a chore than using connection strings.
A DSN is a helpful alternative to connection strings. Consider using a DSN when multiple applications on the same machine reference the same data source, or you lack the expertise to work with connection strings.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at firstname.lastname@example.org.