How do i connect to an Access-database with an ADO-object?
This guide will show you how to connect to an Access-database (.mdb file) with an ADO-object. This technique is especially useful for customers who run Classic ASP and not ASP.NET
For this example, we have a finished Access-database for demonstration. However, If you know the table structure of your .mdb-file, this will be easy to correct. Pay special attention to when we construct an SQL statement below, as that is where the table and database names are used.
Before we begin however, We must point out that we don’t recommend using an Access-database, but instead use MSSQL. This is because Access-databases are not optimized for performance and high numbers of users simultaneously. We highly recommend that you use an MSSQL service instead (we have such services included in all our Windows-based Webhotels). Having a website rely on an .mdb file can negatively impact its performance.
First things first, we have to create an asp-file with the code. We start with a simple html-header.
Now we can start writing the server script that is going to connect to the database. For this, we need 3 variables:
After this we must get an ADO Connection Object. This object is used to feed the variable ‘adoConnection’ with the connectioninfo for the server.
Now we have to open a connection to the database. There are a number of ways to do this, but in this case we will do what is known as “DSN-less”. This is faster and easier to use than DSN in many cases. We are also skipping past a number of configuration steps, which would otherwise be required.
To create a “DSN-less” connection to the database, we have to use adoConnection to open it. The database is a ‘Microsoft Access Database’ – i.e. an .mdb file. To open that, we need to use a driver called ‘Microsoft Access Driver’. The file in our example is called comments.mdb
On the line below, you will see something called ”Server.MapPath” in front of the database name. This is used so that the webpage knows where on the server the file is located. If both the mdb file and the asp file are in the same directory, Server.MapPath will return the physical location of the mdb file. Like so:
After that is done, we have to create what is known as a ’recordset’ for the ADO-object. This recordset is a data structure that will receive the data we request from the database.
To send the question to the database, we use SQL (Structured Query Language). On the next line we will initialize the variable ‘strSQL’ with an SQL-statement. Please keep in mind that when you are using your own .mdb file, you must check what the name of each table and column is called. In our example, we have two very simple columns named ‘Name’ and ‘Comments’ in our database.
Now everything is in place, and we can end our SQL call. As shown below, the "rsDataResult" variable will receive the data we request in our SQL call.
To display the data, we can create a loop. The loop goes through all the lines of our record set, stopping at the end of the file (EOF). The "Response.Write" method is often used to write the contents of the record set to the website. The "MoveNext" method goes to the next line, and the loop starts over. Each time the loop runs, it checks if it has reached EOF. If yes, the loop terminates.
Finally, we'll do a bit of clean up before we close the file. We have to close the record set, clear out the server objects and close our open HTML tags.
Save the file as e.g. "comments.asp", and save it in the same folder as the database, for ease of access.
And that's it! You just created and opened a database connection, requested data, displayed the data on your website, and closed everything down again afterwards.