Support

How to create a DSN-less connection string and say goodbye to system DSN's

Rather than using a non-user configurable system DSN it is easier and more useful to the developer to be in full control of their own DSN management. A DSN, in simple terms. is the method used to connect to a database to enable access to information stored in a database.

What do we need to form our DSN-less connection string? Only a few items of information, we need to know which driver to use, the location of the database and the user id and password if the database is protection with Access security.

• The driver is straight forward, in most cases we use the Microsoft Jet driver e.g. PROVIDER=Microsoft.Jet.OLEDB.4.0.
• The location of the database can be an absolute path e.g. c:\inetpub\wwroot\mydb.mdb but on a remote server you may not know the physical location of the database only it's location relative to the root of the web site.  For relative locations use the ASP directive Server.MapPath(<relative location>)
• The user id is "Admin" and the password left blank for unprotected databases, replace with your own user id and password if you have protected the database.

Let's have a look at an example of a DSN-less connection string:

<%
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("/mydatabases/test.mdb") & ";" & _ 
              "User Id=Admin;" & _
              "Password=;"
%>

That's really all there is to it.

The example above uses MS Access as the source database. However, other databases can be used in much the same way. See http://www.able-consulting.com/ADO_Conn.htm for example of connection strings for a variety of data sources.

FAQ

Q. My database directory lies outside my web directory how do I access it using Server.MapPath().

A. As long as you are in your defined directory structure i.e. you and the anonymous user have permission to access non-web directories then you can navigate your way to any directory. E.g. assuming the script is running in the web root and you want to access the database mydatabase.mdb in a folder name 'databases' one level above the web directory use the format Server.MapPath("../databases/mydatabase.mdb").