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
Whats a DSN ?
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 dont have to remember the database name, where
it resides, etc.
So, whats a file DSN ?
Before we discuss this, let me the list the various types of
- 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
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.
Creating file DSNs and reusing them
Shown below is a generic file DSN for an MS Access database.
------------- file.dsn -------
DRIVER=Microsoft Access Driver (*.mdb)
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
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
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
the same holds for DBQ parameter. UID and PWD are optional.
In summary, to create a reusable file DSN
- Create a text file with DSN parameters. This file has a .dsn extension
- Create the connection string, with the filedsn and DBQ parameters. You can determine
physical paths using Server.MapPath method
- Use the DSN for as many databases you want, by changing just the DBQ, UID and PWD
Note that databases other than MS Access require different values
for parameters and also some additional parameters.