How To Query a MS Access Database Using ASP

September 30th, 2008 · No Comments

Easy CGI - Windows 2008 HostingWhile 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

0 responses so far ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment




Posted in Tutorials · Windows Server | No Comments
Dedicated Servers
  
VPS
Website Hosting
 

Recent Comments

Recent Webmasters
Hosting Type :
Monthly Price :
Storage :
Transfer :
Sort By :