News: - Used by 85,000 Members - SMS Backed up 7,35,000 - Contacts Stored  28,850 !!

Main Menu

Connecting To An Access Database Using ASP

Started by sukishan, Aug 20, 2009, 02:47 PM

Previous topic - Next topic


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.

<title>My First ASP Page</title>
<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 ("
    Response.Write (rsGuestbook("Name"))
    Response.Write ("
    Response.Write (rsGuestbook("Comments"))
    Response.Write ("

    'Move to the next record in the recordset

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
Set rsGuestbook = Nothing
Set adoCon = Nothing


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.
A good beginning makes a good ending