Active Server Corner
Quick Site Search
What's Inside
Home
Activity
Books
Excerpts
In-Depth
CodeLibrary
Columns
ActiveTalk
COMSutra
Cornered!
My 3 Cents
Square ONE
Downloads
Events
FAQ's
Jobs
Search
Tools
Product Reviews
Tutorials
Site Info
About Us
Advertising Info
Contact Us
Privacy Policy
Terms of Use
Write for Us
  <% Tutorials %>  

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.

What’s 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 don’t have to remember the database name, where it resides, etc.

So, what’s a file DSN ?

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.

Creating file DSNs and reusing them

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.

Rate this article
Help us present better articles by rating this article. Thanks !

Very Useful
Useful
Somewhat Useful
No use

SPONSORS

Wrox Press



BOOKS OF INTEREST







Copyright 1997-2000 Active Server Corner. All rights reserved.

Sponsored by Coastline Web Hosting in Santa Barbara, California