Using Microsoft Access with ASP and ADO

To access a database we first need to open a connection to it, which involves creating an ADO Connection object. We then specify the connection string and call the Connection object's Open method.
 
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                   "DBQ=C:\MyDatabases\database.mdb;DefaultDir=;UID=;PWD=;"

To create the ADO Connection object simply Dim a variable.
 
Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")

Then open the connection
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Open ConnectionString
 
Now, to access records in the database, ADO Recordset object has to be defined first
' Create a RecordSet Object
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
' Retrieve the records
rs.Open "SELECT * FROM MyTable", Connection, adOpenForwardOnly, adLockOptimistic
 
adOpenForwardOnly is defined as 0 and specifies that we only wish to traverse the records from first to last.
adLockOptimistic is defined as 3 and allows records to be modified.
 
' This will list all Column headings in the table
Dim item
For each item in rs.Fields
 Response.Write item.Name & "<br>"
next
   
' This will list each field in each record
while not rs.EOF
  
 For each item in rs.Fields
  Response.Write item.Value & "<br>"
 next
  
 rs.MoveNext
wend
 
End Sub

But, we should always remember to close our recordsets and connections.
rs.Close
set rs = nothing
Connection.Close
Set Connection = nothing