Scripting Access with ASP

ASP Scripting With Access Databases

General Scripting Information

The scripting information provided here is aimed at Espinda' customers. Much of the code is applicable to any Windows based Web Server. However some parameters, such as the methods used to derive file paths, may not be valid.
Our ASP scripts are all written in VBS; we provide no JScript equivalents. We do provide Perl equivalents using the same technologies. Perl users should use the same OLE objects.

The current version of ADO installed on our servers is MDAC 2.7. Detailed component information can be obtained from Microsoft or one of the many scripting sites on the Web. Search on google for "recordset.movenext" or something similar to find them.

Before looking at the code, the following points should be considered when using Access databases in a shared hositng environment:

* All scripts run on a shared server, and thus what happens on one site has an effect on the stability of certain components on other sites on the same server. Access ADO is such a component. It is a shared resource, and should be programmed correctly.

* The Jet access engine has a limited set of resources. Once these are exhausted, the script will generate errors. These are normally temporary in nature and can occur when server loading is very heavy. These are operating system limitations, and cannot be extended or isolated by us.

* You should use only Access 2000 or higher databases. If you use Access 97 you may receive the following error:-
[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.
This is due to an issue in MDAC 2.5 and greater and is only resolved by using a newer version of Access.

* Use as few connections as possible. If you are using session and application based objects, use a single application based connection. Otherwise, use one per script, explicitly opening the connection at the start of the script, and then closing it at the end of the script.

* Always use OLEDB connection strings instead of DSN connections. The provider to use for Access is "MICROSOFT.JET.OLEDB.4.0". See the connection string used below.

Using ADO in ASP to connect to an Access Database on Your Site

Now to the code. First intialize the variables. This isn't strictly necessary, but it's good programming practice to do so.

dim conn, strsql, rsuser, strMDBPath
set conn=server.createobject("ADODB.Connection")
set rsuser=server.createobject("ADODB.Recordset")

Next set up the database connection. Always explicitly open the connection. Do not use a connection string with objects such as a command or a recordset object. These implicitly open a database connection which they then leave open to time out after the script closes. This connection accesses the Northwinds database which is stored in the Private directory below the web site root folder HTDOCS. Note the use of the Server.MapPath method to derive the correct path on the server.

strMDBpath = Server.MapPath("..\private\northwind.mdb")
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

Now open the resordset using the current connection. In this case we are querying the customer table.

strsql = "select top 10 (city) from customers"
rsuser.open strsql,conn,1,2

With the recordset now open, we iterate through it and output the results to the page. Note that we don't need to use the movefirst method as the recordset points to the first record by default when opened. With an empty recordset, the loop exits immediately without errors.

Do while not rsuser.eof
response.write rsuser("City") & "<br>"
rsuser.movenext
Loop

Finally we close the objects used.

rsuser.close
conn.close
set rsuser=nothing
set conn = nothing