|
|
 |
|
Lesson 17: Connecting to an Access database |
|
Lesson 17: Connecting to an Access database
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.
| |
|
|
|
|
