Programming Articles

Connecting to MS Access in ASP with a DSN

This script demonstrates connecting to a MS Access database in ASP with a DSN. The script will make the connection and then output all the rows of a specified table.

Dim objConn, rsRecord, strSQL, tableName, dsnName, dsnUser, dsnPass
Define the variables we will use in this script.

Set objConn = Server.CreateObject("ADODB.Connection")
Create the connection object. We will use ADO for the connection.

dsnName = "testdsn"     'The name of the DSN
dsnUser = "testuser"     'The username for the DSN
dsnPass = "testPass"     'The password for the DSN
tableName = "testit1"     'The table in the database to display
Sets the parameters to connect to the database, these should be changed to the values appropriate for your database.

objConn.Open "DSN="&dsnName, dsnUser, dsnPass
Opens the connection to the database.

The next lines check if an error occurred in the connection; if it did then the error is displayed, otherwise a success message is displayed.

Set rsRecord = Server.CreateObject("ADODB.Recordset")
Here we create a recordset that will hold the results of the database query.

strSQL = "SELECT * FROM " & tableName
Create the SQL string which will select all of the rows from the table that was specified.

rsRecord.Open strSQL, objConn
This line runs the query on the database connection and stores the results in the recordset.

Response.Write "<TABLE BORDER=1>"
The results of the query will be put into an HTML table.

'Write out Field Names
Response.Write "<TR>"
For i=0 to rsRecord.fields.count-1
     Response.Write "<TH>"+rsRecord(i).Name+"</TH>"
Next
Response.Write "</TR>"
This code will loop through each of the fields and displays the names in the first row of the table.

'Write out Data
Do while not rsRecord.eof
     Response.Write "<TR>"
     For i=0 to rsRecord.fields.count-1
          Response.Write "<TD>"
          Response.Write rsRecord(i)
          Response.Write "</TD>"
     Next
     Response.Write "</TR>"
     rsRecord.movenext
Loop
This code will loop through each row in the table and print out the values in a row in the HTML table.

rsRecord.Close
objConn.Close
Set rsRecord = Nothing
Set objConn = Nothing
Closes the database connection and unassigns the variables.

--Here is the full script, the filename should be: access-dsn.asp--

<%@ LANGUAGE="VBSCRIPT" %>

<HTML>
<HEAD>

<TITLE>Connecting to an Access database with a DSN in ASP</TITLE>
</HEAD>
<BODY>

<%
'Easy CGI Test script for connecting to an Access database in ASP with a DSN
'This script will connect to the database and output the results in an HTML table

'Some Variables
Dim objConn, rsRecord, strSQL, tableName, dsnName, dsnUser, dsnPass
Set objConn = Server.CreateObject("ADODB.Connection")

'Change these to point to your database
dsnName = "testdsn"     'The name of the DSN
dsnUser = "testuser"     'The username for the DSN
dsnPass = "testPass"     'The password for the DSN
tableName = "testit1"     'The table in the database to display

'Connect to database with DSN

Err.clear
objConn.Open "DSN="&dsnName, dsnUser, dsnPass

'Check error
if (Err <> 0) then
     Response.Write "Error connecting to DSN: " & dsnName
else
     Response.Write "Connected to DSN: " & dsnName
end if

'Create a recordset for a query
Set rsRecord = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM " & tableName

rsRecord.Open strSQL, objConn

'write out a table in html to display the table
Response.Write "<TABLE BORDER=1>"

'Write out Field Names
Response.Write "<TR>"
For i=0 to rsRecord.fields.count-1
     Response.Write "<TH>"+rsRecord(i).Name+"</TH>"
Next
Response.Write "</TR>"

'Write out Data
Do while not rsRecord.eof
     Response.Write "<TR>"
     For i=0 to rsRecord.fields.count-1
          Response.Write "<TD>"
          Response.Write rsRecord(i)
          Response.Write "</TD>"
     Next
     Response.Write "</TR>"
     rsRecord.movenext
Loop

Response.Write "</TABLE>"

'Reset server objects
rsRecord.Close
objConn.Close
Set rsRecord = Nothing
Set objConn = Nothing

%>

</body>
</html>
</SPAN>

--End Script--
1/5/2008 1:49:01 AM Category Database Connection Strings Comments 0

Back