How To Query a MS Access Database Using ASP

September 30th, 2008 · 4 Comments

While using a Microsoft Access database to feed a high-traffic website may not be a good idea, it can be very useful to develop a small intranet/extranet application for your business. Lately I’ve got myself a hosting account with Easy CGI, a popular Windows Hosting provider, and tried to query a MS Access database using ASP.

This tutorial will show you how to open a connection to a MS Access database and perform a SQL query.

I will not go into details about creating a MS Access database, but for this post I’ve used a very simple structure made of one table (mytable) with two fields (id and data).

Of course the first you want to do is to upload your database to your server using FTP. Then you’ll need to create an ASP script using a text editor like Notepad for instance. Ok let’s go!

1. Start your script by creating the variables that we’re going to use:

<html>
<head><title>Querying a MS Access Database</title></head>
<body>
<%
Dim adoCon     'Holds the Database Connection Object
Dim rsTable    'Holds the recordset for the records in the database
Dim strSQL     'Holds the SQL query to query the database

2. Next we need to create a database connection object using the ADO (ActiveX Data Objects):

Set adoCon = Server.CreateObject("ADODB.Connection")

3. Now we’re going to open the database. Note that you must specify the path and the name of your own database here:

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("mydatabase.mdb")

4. Create an ADO recordset object:

Set rsTable = Server.CreateObject("ADODB.Recordset")

5. We’re now going to set our SQL query string:

strSQL = "select * from mytable;"

6. Let’s open the recordset and run our SQL query:

rsTable.Open strSQL,adoCon

7. Now you need to create a loop that will go through the recordset:

Do While not rsTable.EOF
	Response.Write(rsTable("id"))
	Response.Write("<br />")
	Response.Write(rsTable("data"))
	Response.Write("<br /><br />")
    rsTable.MoveNext
Loop

8. And finally we’ll close the recordset, reset the server objects and close the scripting tag:

rsTable.Close
Set rsTable = Nothing
Set adoCon = Nothing
%>
</body></html>

When you’re done, simply upload your ASP script to your server and use your web browser to see the result.

Of course, if you’re going to store a large amount of data or if your website gets a lot of traffic, you’re better off with a database server such MS SQL or MySQL as it will deliver a lot more speed and performance.

Links

Bookmark It

4 responses so far ↓

1. Response by : shane on Jan 22, 2010 at 4:36 pm

This is a really great article. I appreciate that you took the time to put it together.

I am still missing one step though. What about creating a little form where a user enters a few bits of information for the query?

How would I go about passing the user’s entry along when they click a submit button?

Thanks for any help.

2. Response by : harish on Apr 19, 2010 at 8:10 am

i also need help on same topic, pls if u can specify with full details as i m new to this, like where to specify my server’s path etc, also if u can demonstrate with a demo program u can use http://www.abcxyz.com as an example

PLEASE HELP ME

3. Response by : harish on Apr 20, 2010 at 3:51 am

please provide me a sample project of how to access a MS Access Database using ASP from server using http://www.abcxyz.com/mytbl.mdp as a dummy database. myemail is harish_sharma2006@yahoo.co.in

4. Response by : The Web Hosting Hero on Apr 21, 2010 at 1:24 pm

@harish : Sorry I don’t have any sample project available.