Setting up an OLE DB connection to an Access database

ASP / Access

OLE DB connections not only offer greater performance than ODBC (DSN) , but also allow some additional functions and have the benefit that you do not need to wait for your web hosting provider to set up a DSN on your server.

First create a new text file, by default windows will name it New Text Document.txt, rename the file NewConnection.udl, or whatever you wish, the important thing is to change the file extension to .udl (Universal Data Link). Windows will give you a warning message about renaming file extensions, click Yes to proceed.

Double click the newly created .udl file, this will open the Data Link Properties box shown below:

Select Microsoft Jet 4.0 OLE DB Provider from the list shown and click the Next>> button.

To connect to an access database on your local PC, click the button with the oblique next to the Select database name text box and browse to your database.

Click the Test Connection button to confirm that everything is working correctly and click OK. Now right click the .udl file and select Open with Notepad.

You should see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WebDir\DatabaseDir\YourDB.mdb;Persist_
Security Info=False

You can now copy and paste the connection string wherever it's required, for example:

<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=_
D:\WebDir\DatabaseDir\YourDB.mdb;Persist Security Info=False"
varInsertCommand = "INSERT INTO tableName(TextField, NumericField)_
VALUES('ABCDE', 12345);"
commInsert.Execute(varInsertCommand) ' Execute the insert command
%>

Connecting to the remote database

Obviously, once you have uploaded your site files and the database to your web server the connection string will no longer work if the directory structure is different.

If you know the directory structure of your web server you can simply replace the relevant portion of the connection string:

D:\RemoteWebDir\RemoteDatabaseDir\YourDB.mdb

If you are unsure of the directory structure you can use Server.MapPath to give you the directory path. Create an ASP page similar to the one below, upload it to your web space, then navigate to the page in your browser. Click here to see a demonstration of the page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
< html>
< head>
< title>MaPath File</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>
< body>
< %= Response.Write(Server.MapPath("/DatabaseDirectory/YourDB.mdb")) %>
< /body>
< /html>

The page will show the directory structure of the web server, you can then use this in your connection string.

Notes for Dreamweaver users

Follow the procedure outlined in the first section, then when you define your site select Custom Connection String.

Name your connection and paste the connection string created in the .udl file into the Connection String text box and click OK.

This will define the connection to the database on your local PC. When you upload the site to the remote host server open the remote copy of file YourConn.asp in the Connections directory, it should look something like this:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_YourConn_STRING
MM_YourConn_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=_
D:\WebDir\DatabaseDir\YourDB.mdb;Persist Security Info=False"
%>

Modify the path stored in the MM_YourConn_STRING variable to the path to your database on the remote web server, you can use the Server.MapPath method shown in second section if required. Save the file and your site should work correctly at the remote host. When you modify files locally and upload to the server, take care not to upload the Connections folder and overwrite the remote version of the connection file.