Scribd
Upload
482 views

Connecting To An Access Database Using Classic ASP

This document describes how to connect to a Microsoft Access database from a Classic ASP page. It involves creating an Access database called "guestbook.mdb" with three fields to store guestbook entries. The ASP page uses ADO to open a connection to the database, execute a SQL query to retrieve entries, and output the results to the page within a loop. Connecting using a DSN-less connection string that maps the physical path is demonstrated. The page provides a simple example of connecting to and querying an Access database with ASP.

Uploaded by

Siva Ji
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
482 views

Connecting To An Access Database Using Classic ASP

This document describes how to connect to a Microsoft Access database from a Classic ASP page. It involves creating an Access database called "guestbook.mdb" with three fields to store guestbook entries. The ASP page uses ADO to open a connection to the database, execute a SQL query to retrieve entries, and output the results to the page within a loop. Connecting using a DSN-less connection string that maps the physical path is demonstrated. The page provides a simple example of connecting to and querying an Access database with ASP.

Uploaded by

Siva Ji
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 9

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 'Blank Access
Database' from the starting menu. You will be prompted for a name for the database and where
you want it saved. Call the database 'guestbook.mdb' 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 'Create table in design view'.

You now need to create 3 fields for the database and select their data types.

Field 1 needs to be called 'ID_no' and have the data type of 'AutoNumber'. Also set this field as
the primary key.

Field 2 needs to be called 'Name' and have the data type of text.

Field 3 needs to be called 'Comments' 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 'General' tab in the 'Field
Properties' 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
'tblComments'.

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.
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGuestbook 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database

Next we need to create a database connection object on the server using the ADO Database
connection object.

'Create an ADO 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 'Microsoft Access
Driver' to open the database 'guestbook.mdb'.

You'll notice the ASP method 'Server.MapPath' 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. 'c:\website\', 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.

'Set an active connection to the Connection object using a DSN-less connection


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

If on the other hand you want to use a slower DSN connection to the database then you will need
to replace the line above with the one below. Also if you don't know how to setup a system DSN
you will need to read my tutorial on, Setting up a System DSN.
'Set an active connection to the Connection object using DSN connection
adoCon.Open "DSN=guestbook"

Next create an ADO recordset object which will hold the records from the database.

'Create an ADO recordset object


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 'strSQL' with an SQL query to read in the fields 'Name' and 'Comments'
form the 'tblComments' table.

'Initialise the strSQL variable with an SQL statement to query the database
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.

'Open the recordset with the SQL query


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 'Response.Write' method is used to output the
recordset to the web page. The 'MoveNext' 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.

'Loop through the recordset


Do While not rsGuestbook.EOF

'Write the HTML to display the current record in the recordset


Response.Write ("<br>")
Response.Write (rsGuestbook("Name"))
Response.Write ("<br>")
Response.Write (rsGuestbook("Comments"))
Response.Write ("<br>")

'Move to the next record in the recordset


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.

'Reset server objects


rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>

</body>
</html>

Now call the file you have created 'guestbook.asp' and save it in the same directory folder as the
database, don't forget the '.asp' 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 'ODBC Drivers'
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.
This step-by-step article describes how to connect to a database by using an Active Server Pages
(ASP) Web page.

Create a Data Source Name

To connect to a database by using ASP pages, you must first create a Data Source Name (DSN)
on the Web server for the type of database to which you want to connect. To do so, use one of
the following methods.

For a Database Program

To create a DSN for a database program (such as Microsoft Access):

1. Log on to the Web server computer as administrator.


2. Click Start, point to Settings, and then click Control Panel.
3. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
4. Click the System DSN tab, and then click Add.
5. Select the database driver that you want (for example,
Microsoft Access Driver (*.mdb), and then click Finish.
6. In the Data Source Name box, type the name that you want to use when you refer to this
DSN in your ASP code. For example, Northwind.
7. In the Description box, type an optional description for the DSN. For example,
Northwind DSN.
8. Click Select.
9. In the Select Database dialog box, browse to and select the database that you want. For
example, Northwind.mdb.

NOTE: If the database is not on the Web server, click Network, and then click Browse.
Locate the shared network folder that contains the database, and then click OK.
Click Finish, and then select the database that you want.
10. Click OK.
11. Click Advanced.
12. If you want to automatically provide login credentials to the database when you use this
DSN, type them into the Login name and Password boxes. Click OK.
13. Click OK, and then click OK.

For a Database Server

To create a DSN for an SQL Server:

1. Log on to the Web server computer as administrator.


2. Click Start, point to Settings, and then click Control Panel.
3. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
4. Click the System DSN tab, and then click Add.
5. Select SQL Server, and then click Finish.
6. In the Name box, type the name that you want to use when you refer to this DSN in your
ASP code. For example, Northwind.
7. In the Description box, type an optional description for the DSN.
8. In the Server list, do one of the following:

 Select the name of an SQL Server on the network.


 Select (local) if the SQL Server that you want is running on the Web server computer.
 Type a name (alias) for a server that does not appear in the Server list.
9. Click Next.
10. Under How should SQL Server verify the authenticity of the login ID, click the
authentication method that you want. For example, With Windows NT authentication
using the network login ID. Click Next.
11. Click to select the Change the default database to check box, select the database that you
want from the list (for example, Northwind), and then click Next.
12. Click Finish, click OK, and then click OK.

Create an ASP Script to Connect to the Database by Using the DSN

In your ASP script, create a connection to the database by using the ActiveX Data Objects
(ADO) Database Access Component (DAC):

 Use the Connection ADO object to create a connection to the database.


 Use the Recordset object to retrieve, update, and delete existing database records.

Example
The following example illustrates how to connect to the Microsoft Access NorthWind sample
database by using an ASP script.

NOTE: This example assumes the default installation of Windows 2000 on drive C, as well as
the default installation of Microsoft Access along with the NorthWind sample database.

Step 1: Create a DSN

Follow the steps in the For a Database Program section of this article to create a DSN to the
Microsoft Access Northwind.mdb sample database.

NOTE: By default, the Northwind.mdb file is located in the C:\Program Files\Microsoft


Office\Office\Samples folder.

Step 2: Create an ASP Page

1. Start Notepad.
2. In Notepad, type the following code:
<HTML>
<HEAD><TITLE>ASP Database Connection</TITLE></HEAD>
<BODY BGCOLOR=white>
<H1>Northwind Database Contacts</H1>
<%
Dim Connect, selectSQL, RecSet
Set Connect = CreateObject ("ADODB.Connection")
Connect.Open "DSN=Northwind"
selectSQL = "SELECT * FROM Customers"
Set RecSet = Connect.Execute (selectSQL)
If NOT RecSet.EOF
THEN
DO UNTIL RecSet.EOF
Response.Write RecSet("Companyname") & ", " & RecSet("Contactname") &
"<BR><BR>"
RecSet.MoveNext
Loop
End If
RecSet.Close
Connect.Close
Set RecSet = Nothing
Set Connect = Nothing
%>
</BODY></HTML>
3. On the File menu, click Save As.
4. In the Save As dialog box, navigate to C:\Inetpub\wwwroot in the Save in list, select All
Files in the Save as type list, type
database.asp in the File name box, and then click Save.
5. Quit Notepad.

Step 3: Test the ASP Page

1. Click Start, and then click Run.


2. In the Open box, type http://localhost/database.asp, and then click OK. A Web page that
displays the NorthWind sample database customer list is displayed in the browser window.

Troubleshooting

If you experience difficulty connecting to a database by using ASP Web pages, verify that you
have sufficient permissions to access the database:

 Verify that the DSN is using an account with sufficient permissions to access the database.
 If you attempt to connect to a SQL Server over a network, ensure that you use a domain
account as your anonymous IIS account.
 If you attempt to connect to a Microsoft Access database, ensure that the IIS account has
Write permissions to the folder in which the database is stored. This is to enable the creation
of a temporary file when users access the database.

You might also like