Displaying Access records with ADO

This page keeps the original Access/ADO example for legacy-maintenance learning. For a new server-side application, prefer a server database such as SQL Server. If you are maintaining an existing Access-backed ASP site, loop through the returned Recordset until EOF becomes true and encode values before sending them to HTML.

Open the Access connection

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
  Server.MapPath("db/emp.mdb")
objconn.Open

Select the fields to display

strSQL = "SELECT emp_no, name FROM emp_m ORDER BY emp_no"

Write each row

Response.Write Server.HTMLEncode(CStr(objRS("emp_no"))) & " " & _
  Server.HTMLEncode(CStr(objRS("name"))) & "<br>"

Example output

145 John Reid
245 Peter Horn
175 Rabin Pack
187 Ran Keth

Complete Classic ASP example

Dim objconn, objRS, strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
  Server.MapPath("db/emp.mdb")
objconn.Open

Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT emp_no, name FROM emp_m ORDER BY emp_no"
objRS.Open strSQL, objconn

Do While Not objRS.EOF
  Response.Write Server.HTMLEncode(CStr(objRS("emp_no"))) & " " & _
    Server.HTMLEncode(CStr(objRS("name"))) & "<br>"
  objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objconn.Close
Set objconn = Nothing

Server.HTMLEncode() is used here because database content can still contain characters that should not be interpreted as HTML.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: