Example Connection to a Microsoft Access Database

This short introduction should help you to realize a database connection with Microsoft Access. The example has been deliberately chosen to make it easier for you to get started.

First create a simple Access database on your local system, which contains a table "Products" with the fields "ID", "Product name", "Product description", "Price" as an example. After completion, copy the Access database (e.g. "Products.mdb") via FTP to the server in your home directory or a folder contained therein.

Please note: By default, the Internet Guest Account has read access to the Access file. If data records of scripts/applications on the server are to be changed, you must also assign write permissions for the file. To do this, use the Webfiles tool in IONOS.

Access via DSN (Data Source Name) to your Access database is not possible. Please use only the method described in this article.
Also, access to Access databases from ASP.net is not possible due to the unmanaged code (a code not managed by the.net runtime). To do this, use the SQL Server Import Wizard in IONOSto make your data available via the SQL server.

Once the desired rights have been set, the database can be integrated into Active Server Pages (ASP).

<html>
<title>Datenbank-Abfrage mit ASP</title>
<body bgcolor="FFFFFF">
<h2>Abfrage der Tabelle <b>Produkte</b> mit ASP</h2>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=e:\kunden\homepages\99\d1234567\Produkte.mdb;"
Set RS = oConn.Execute("SELECT * FROM PRODUKTE")
While Not (RS.EOF)
Response.Write RS("Produktname") &amp; " - " &amp;
RS("Produktbeschreibung") &amp; " - " &amp;
FormatCurrency(RS("Preis")) &amp; "<BR>"
RS.MoveNext
WEnd
RS.Close
Set RS = Nothing
oConn.Close
Set oConn = Nothing
%>
</body>
</html>

The ASP code always starts with

<%

and ends with

%>

In ASP you use the object Variable oConn for database connection (ADODB.Connection). With this object you open and close the connection and query the database. The principle corresponds to a telephone call: You establish a connection, retrieve all desired information and close the connection again. For security and performance reasons, please make sure that you do not leave the connection open longer than necessary.

The oConn.Open statement opens the connection with the specified driver (Jet.OLEDB.4.0). In addition, the absolute path to the Access file is passed. You get the path e.g. with the call Server.MapPath:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; Server.MapPath(".") &amp; "\Produkte.mdb;"

Next, use the open connection to make the desired SQL query: SELECT * FROM PRODUCTS. The oConn.execute function executes the query and makes the result available to you as a recordset (RS).

You can read the individual rows of your recordset in a while loop. Important is the command RS.MoveNext, which jumps to the next data set. Without this command, you always read the first line of the result in an endless loop.

Finally, close all connections used and delete the corresponding objects to avoid performance losses and errors:

RS.Close
Set RS = Nothing
oConn.Close
Set oConn = Nothing