File DSNs - Use and Reuse

by Manohar Kamath

Many a time, you are confronted with creating a system DSN for use with ASP. If you have an ISP account, this might take time and more than sometimes - money. This article describes how to use File DSNs and save time and money. Also, you can reuse these DSNs and thus, with some modifications, use the same DSN for as many database connections.

According to Microsoft documentation, it means "The name that applications use to request a connection to an ODBC data source." In other words, it is a symbolic name that represents the ODBC connection. It hides the connection details like database name, directory, database driver, UserID, password, etc. So when making a connection to the ODBC, you don’t have to remember the database name, where it resides, etc.

Before we discuss this, let me the list the various types of DSNs.

• System DSN: This is the DSN used through out the system, no matter which user logs in. Meaning, all users on the system can see this DSN and use it (as long as they know the user ID and password). The connection parms are stored in the registry.
• User DSN: This is a DSN created for a specific user. Only the user who created the DSN can see this and use it. Like System DSN, the information is stored in the registry.

In both these two cases, DSN details are stored in the system registry.

With that, let me explain what a file DSN is. Instead of storing the DSN details in registry, you store it in a file - simple ?! Yes, it is. The file is a simple text file, with a .DSN extension.

Although it sounds simple, you need to know the parameters that make up a DSN. But again, it is not all that difficult, as you can look this up in any ODBC reference, or online help that comes with Visual Basic or Access.

Shown below is a generic file DSN for an MS Access database.

------------- file.dsn -------
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
ReadOnly=0
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25

I call the file file.dsn, you may rename it appropriately.

Creating this file is half work done. The next step is creating a connection. When using a system DSN, you create a connection by simply using the system DSN name, something like

Conn.Open "MyDSN"

While using the file DSN shown above, you need a few more parameters I purposely left out so that you could reuse the file DSN. The final connection string will look like

"filedsn=c:\webdir\file.dsn;DBQ=c:\database\mydb.mdb;UID=admin;PWD=admin;"

c:\webdir is the directory where file DSN resides. This requires a physical path to the file. On an ISP, you could use Server.mapPath method to determine this directory. Something like

Server.MapPath("/mysite/file.dsn")

the same holds for DBQ parameter. UID and PWD are optional.

In summary, to create a reusable file DSN

1. Create a text file with DSN parameters. This file has a .dsn extension
2. Create the connection string, with the filedsn and DBQ parameters. You can determine physical paths using Server.MapPath method
3. Use the DSN for as many databases you want, by changing just the DBQ, UID and PWD parameters

Note that databases other than MS Access require different values for parameters and also some additional parameters.