Guides

Connections to the Microsoft Access database

Examples in ASP e PHP code

The MS Access database is available on all Windows hosting plans. Below are examples of connections to the MS Access database with ASP (VBscript) and PHP languages.

In order to use an MS Access database with an application or website, the database and the folder in which it is located must have write permissions, even if the application uses it in read-only mode. In fact, the Microsoft libraries that manage access to the database create temporary files (*.ldb) in the same folder as the database (*.mdb), and if there are no write permissions, the IIS web server will return an error 500.

It is not recommended to place the MS Access database in the main (root) folder because, as mentioned above, this would mean applying write permissions to the root of the website, which is extremely risky in terms of security.

MS Access is a database that is easy to manage: it is easy to create and easy to back up (for example, by downloading it via FTP to the local PC), but its use is limited by its size and the number of transactions. Moreover, it is limited on the number of connections it can activate simultaneously and on its performance.

dsn-less connection using ASP

Let's use the symbol "/" to indicate the site's main folder (root) and assume that the database is called esempio.mdb, located in the /database/ folder of the site. An example of a connection and reading of a table could be as follows:

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _ server.MapPath("/database/esempio.mdb") sql = "SELECT * FROM Tabella1" Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn %>

dsn-less connection with PHP

Thanks to php's COM class, it is possible to connect to an MS Access database using the ADODB library.

In the example that can be downloaded from this link, it is assumed that there is a test database, test.mdb, in the path /testdb/,which is a subfolder of the location of the adodb.php script included in the .zip file.

Positioning the connection script

An important aspect to highlight when connecting to a database from a web application is the positioning of the script that opens the connection and manages the recordset returned by the queries: this code must be positioned before the tag and must be responsible not only for extracting the data but also for destroying the recordset objects used and the connection created.

In this way, in fact, the server will complete the data extraction (and the closing of the connection) before sending the page to the client, speeding up the loading of the page and therefore navigation. In particular, the elapsed time is also reduced between the opening and closing of the connection, thus minimizing the possibility of too many simultaneous connections to the database, MS Access critical point.