-Log in
-Register (free)


-RSS Feeds
-New MyDesk Module
-Update to Profile

Alternate color for RS looping with For Next
Northwind / ASP - Nested select
ASP SQL Injection




 Lesson 17: Connecting to an Access database
Lesson 17: Connecting to an Access database

Author: Alexander Haneng
Difficulty: Medium
Requires: ASP, MS Access97 or 2000
Demo: n/a
Download: Lesson17.zip
Summary:
Learn to connect an MS Access database to you ASP pages.

Intro
This is a long awaited lesson about connecting an Access database with ASP pages. In this lesson I will focus on connecting Access with PWS 4.0 on a Win95 machine. You can use the same procedure on Win98. I will also learn you how to create a database table in Access and how to retrieve it's content and display it through ASP.

Installing
I assume that you already have installed PWS 4.0 on your machine. If you haven't and need some help, check out my article over at ASP101: Installing PWS. The installation of Access should be straightforward, just select full installation, and follow the install wizard.

Creating a database table
The first thing to do is creating a database table in Access. Lets open Access. You will find it on the start menu under programs, look for the Access icon: When you start Access, it will ask you what you want to do. Choose "Blank Database" and click OK. Now Access asks where you want to save the database file (the mdb file) and what to call it. Call it MyDatabase.mdb, and save it in My Documents folder. A window will now pop up, click New to make a new table in your database. Select "Datasheet View" and click OK. Access will now display your new table:



It looks a bit like an Excel spreadsheet, but it's a database table. Every database contains several tables which holds your information. I'm going to build a database over my friends' homepages. And I want to store their name and the link to their homepage. First we change the name of the field called "Field1" to "Name". To do this just double click where it says Field1, then type in Name and press the Enter key. Do the same to "Field2", and call it "Link". When your done it should look like this:



Now you can type in the name of your friends and the link to their homepage. I type in my friends Kenneth and Arnstein and the links to their homepages. My table looks now like this:



Now we are going to save our table. Just go to the File menu, and click save. Access will now prompt you for a name, type in Friends and click OK. Click NO to the next question. And voila! you have made your first database!!

Making an ODBC connection
Now we are going to make the connection that our ASP scripts are going to use when talking to the database we have created. Start by going to the Control Panel (Start -> Settings -> Control Panel). In the control panel you should find an icon called "32bit ODBC", it looks like this: Double click it and this window will pop up:

It's the "ODBC Data Source Administrator" window. Click the "File DSN" tab at the top. You should now see this:

Click the Add button. You will now see a list of different ODBC drivers, select "Microsoft Access Driver (*.mdb)" and click next. Now it asks for where to save the dsn file and what to call it. Click browse, go to c:\ and create a new directory called dsn. Save it as MyTable_dsn.dsn and click next (the path to the dsn file should now be c:\dsn\MyTable_dsn.dsn). Click finish. It now wants you to select which database to connect to. Click Select and find the mdb file you created with Access. It should be in the My Documents folder. Click OK twice. That's it!

Connecting to MyDatabase
To get information stored in a database with ASP you need to make a connection to the database through ODBC. You can look at it like phoning in to the database. To "dial up" our Access database with VBScript we use this piece of code:
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=c:\dsn\MyTable_dsn.dsn"
%>
We can now search the database with standard SQL query. SQL is a language used to send queries to databases (SQL=Structured Query Language). To retrieve the information from our database we can use this SQL query: SELECT * FROM Friends. Here's how we do this with VBScript:
<%
SQL_query = "SELECT * FROM Friends"
Set RS = MyConn.Execute(SQL_query)
%>
Now we have retrieved everything from the Friends table into a recordset called RS. A recordset is like an array holding all the data we selected from the table. To display it we will use a loop to display one friend at the time from the recordset. We want it to keep looping until there are no more friends left in the recordset. To check if there are any records left in the recordeset we use RS.EOF (EOF = End Of Stream), it is false if there are records left, and true if there is not. The code for displaying the recordset is:
<%
WHILE NOT RS.EOF
%>
<LI><%=RS("Name")%>: <A HREF="<%=RS("Link")%>">Homepage</A>
<%
RS.MoveNext WEND
%>
As you can see in the code above we use RS("Name of Field") to select the fields in the current record. Now that we are finished with the database and the recordset we have to close them. First we close the recordset with RS.Close and then we "hang up" the "phone call" to the database with MyConn.Close.

Click here to download the full source code.

Last comment
Now you have gotten a small start in using the power of databases with ASP. I will probably post some more advanced lessons about Access and MS SQL Server in the near future, until then you should play a bit with Access to get a feel with how things work. Try adding more fields, do more advanced searches with SQL, enter data into the database from ASP and try to page through recordsets (try the code in the code archive)

Where to go next:
Check out the other lessons.


16: Query strings, part II
18: Turning numbers into graphs
| Info |
© Copyright 1997-2014 Alexander Haneng