Codefixer - Connecting to an Access database without a DSN

The code below allows us to connect to our Access database called ‘Friends.mdb’ and retrieve all the records from the table ‘tblFriends’. The table has 3 columns, firstly an 'ID' field that is an autonumber, secondly a field called 'FirstName' which is a textfield and lastly another textfield called 'SurName'.

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Connecting to an Access Database without a DSN</title>
</head>
<body>
<%
'declare your variables
Dim Connection, sConnString
Dim sSQL, Recordset

'declare your SQL statement that will query the database
sSQL = "SELECT * FROM tblFriends"

'create an ADO connection and recordset object
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'define connection string, specify database driver and location of the database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Friends.mdb")

'Open the connection to the database
Connection.Open(sConnString)

'Open the recordset object executing the SQL statement and return records
Recordset.Open sSQL,connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While Not recordset.EOF
Response.Write Recordset("ID") & " " & Recordset("FirstName") & " " & _
Recordset("SurName")
Response.write "<br>" 'insert a line break
'move on to the next record
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects and free up resources
Recordset.Close
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
%>
</body>
</html>

The above code will display the ID number, the Firstname and Surname of all the entries in our database table 'tblFriends'. If there are no entries then the message 'No records returned.' will be displayed.

Connecting to an Access database without a DSN (using a connection string)