Connecting to an Access Database using Classic ASP
If you are reading this page then I shall assume that you already know a little bit
about ASP and running ASP applications.
To make this tutorial more interesting and the following database tutorials on, Adding, Deleting,
and Updating, data from a Microsoft Access database, we are going to use these tutorials
to make a simple Guestbook application.
Before we can connect to a database we need a database to connect too.
Creating the Guestbook Database
To create a database your first need to open Microsoft Access and choose '' from the starting
menu. You will be prompted for a name for the database and where you want it saved. Call the database '' and save it in the same directory as the web page connecting to the database is going to be.
You should now see the main Access dialog box, from here select ''.
You now need to create 3 fields for the database and select their data types.
Field 1 needs to be called '' and have the data type of ''. Also set this
field as the primary key.
Field 2 needs to be called '' and have the data type of text.
Field 3 needs to be called '' and also has the data type of text, but this time you need to change the default
field size of 50 to 100 characters under the '' tab in the '' box
at the bottom of the screen.
Once all the field's have been created and the data types and primary key set, save the table as ''.
Now the table has been created you need to enter some test data into the table. You can do this by double-clicking on the new table (tblComments)
in the main dialog box. From here you can enter some test data. I would recommend entering at least 3 pieces of test data.
If you are having trouble creating the database then you can download this tutorial containing the Access Database with test data already entered.
Connecting to the Guestbook Database
Now that the database is created and test data entered we can get on with creating the web page to display the data from the database.
First we need to start web page, open up your favourite text editor and type the following HTML.
|
|
|
|
<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black"> |
|
|
|
|
Next we can begin writing the ASP to connect to the database. First we need to create the variables that we are going to use in the script.
|
|
|
|
<%
Dim adoCon
Dim rsGuestbook
Dim strSQL |
|
|
|
|
Next we need to create a database connection object on the server using the ADO Database connection object.
|
|
|
|
Set adoCon = Server.CreateObject("ADODB.Connection") |
|
|
|
|
Now we need to open a connection to the database. There are a couple of ways of doing this either by using a system DSN or a DSN-less connection.
First I am going to show you how to make a DSN-less connection as this is faster and simpler to set up than a DSN connection.
To create a DSN-less connection to an Access database we need tell the connection object we created above to open the database by telling the connection
object to use the '' to open the database ''.
You'll notice the ASP method '' in font of the name of the database. This is used as we need to get the
physical path to the database. Server.MapPath returns the physical path to the script, e.g. '', as long
as the database is in the same folder as the script it now has the physical path to the database and the database name.
|
|
|
|
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("guestbook.mdb") |
|
|
|
|
Next create an ADO recordset object which will hold the records from the database.
|
|
|
|
Set rsGuestbook = Server.CreateObject("ADODB.Recordset") |
|
|
|
|
To query a database we need to use SQL (Structured Query Language). In the next line we initialise the variable '' with an SQL query to read in the fields '' and '' form the '' table.
|
|
|
|
strSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;" |
|
|
|
|
Now we can open the recordset and run the SQL query on the database returning the results of the query to the recordset.
|
|
|
|
rsGuestbook.Open strSQL, adoCon |
|
|
|
|
Using a 'Do While' loop we can loop through the recordset returned by the database while the recordset is not at the
end of file (EOF). The '' method is used to output the recordset to the web page. The '' method of the recordset object is used to move to the next record in the recordset before looping back round to display the next record.
|
|
|
|
Do While not rsGuestbook.EOF
Response.Write ("<br>")
Response.Write (rsGuestbook("Name"))
Response.Write ("<br>")
Response.Write (rsGuestbook("Comments"))
Response.Write ("<br>")
rsGuestbook.MoveNext
Loop |
|
|
|
|
And finally we need to close the recordset, reset the server objects, close the server side scripting tag, and close the html tags.
|
|
|
|
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>
</body>
</html> |
|
|
|
|
Now call the file you have created '' and save it in the same directory folder as the database, don't
forget the '' extension.
And that's about it, you have now created a connection to a database and displayed you Guestbook in a web page, now to find out how add comments
to the Guestbook through a web form read the next tuorial on, Adding Data to an Access Database.
If you find that you are getting errors connecting to the database then please read through the Access
Database Errors FAQ's, practically make sure you have the correct '' installed on your system and if
you are using the, 'NTFS file system', make sure the permissions are correct for the database and the directory the
database in.
Part 2: Adding Data to an Access Database (Guestbook Pt.2) >>
Accompanying Tutorials in this Series
Now that you have completed this part you should look at the accompanying Tutorials in this series
|