Welcome to Creative Brain - Home of all IT needs

Connecting to MS Access in ASP without a DSN

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

Dim objConn, dbPath, rsRecord, strSQL, tableName
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.

dbPath = "/db/test.mdb" 'The virtual path to your database file
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 "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath(dbPath)
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-dsnless.asp--

<%@ LANGUAGE="VBSCRIPT" %>

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

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

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

'Change these to point to your database
dbPath = "/db/test.mdb" 'The virtual path to your database file
tableName = "testit1" 'The table in the database to display

'Connect to database without DSN
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath(dbPath)

'Check error
Err.clear
if (Err <> 0) then
Response.Write "Error connecting to database: " & dbPath
else
Response.Write "Connected to database: " & dbPath
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>"

Response.Write "</TABLE>"

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

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

--End Script--

<< Back