Comparison of DAO and ADO Recordset Syntax home page

These examples provide DAO programmers with a brief reference to ADO syntax.

You are the correct audience for this page if you are using ASP recordset programming. The recordset interface is referred to as ActiveX Data Objects (ADO). It requires a different syntax than DAO, but your experience with DAO will transfer nicely, and it has a much more powerful and consistent toolkit for accessing today's variety of PC and mainframe database formats on the Web.


Say thanks for the Recordset Syntax.

Visit our e-book
project site.

How to use these examples

Because they are organized into functional topics and kept very short, none of the examples can stand alone. They won't run unless you combine them. A minimum program would need to use a combination of the "Opening", "Displaying" and "Closing" topics. You can cut and paste the short program topics into a working program. And you can copy and paste the data used in the examples from the table below.


Example Data (tblPeople)

pkPeople
ID  FirstName LastName Hire Review  Salary      Sex    IsSelected
1   Carla   Dumont  9/4/87  10/2/99 $60,249.82  F   Yes
2   Andrew  Frank   2/9/97  2/9/99  $55,081.10  M   Yes
3   Janet   Lydell  6/25/94 6/25/99 $49,875.00  F   No
4   Margo   Oniell  1/16/94 7/16/99 $77,629.58  F   Yes
5   Edward  Jones   1/17/98 9/17/99 $40,163.31  M   No
6   Harry   Jones   9/22/78 10/1/99 $103,500.00 M   Yes
7   Jane    Doe     8/9/78  10/3/99 $103,750.00 F   Yes
8   Hugh    Poynor  9/12/89 9/30/99 $30,601.00  M   No
9   Jane    Deaux   9/8/87  10/3/99 $79,368.71  F   Yes

How to use the example data in Access

If you have this document open, highlight all 9 data rows (above) and copy them to the clipboard. In Access create a new blank table and highlight 8 datasheet columns. Paste the example data into the table and rename the fields to the names above. Be sure and name the new table tblPeople. If you have the examples only in hard copy, you would have to re-type the data in an Access table.

Workstation Server
DAO ADO ADO
Opening Opening Opening
closing closing closing
Syntax for Fields Syntax for Fields Syntax for Fields
Displaying a Recordset Displaying a Recordset Displaying a Recordset
Editing a Recordset Editing a Recordset Editing a Recordset
Adding New Records Adding New Records Adding New Records
Deleting Records Deleting Records Deleting Records
Find First Record Find First Record Find First Record
Find Last Record Find Last Record Find Last Record
More Syntax for Finding Records More Syntax for Finding Records Download adovbs.inc
Recordset to Array Recordset to Array Download adovbs.inc
SQL SQL SQL
Execute SQL Execute SQL Execute SQL
  Filtering Records Filtering Records
Combining SQL and Recordsets Combining SQL and Recordsets Combining SQL and Recordsets
  ADO Open Method: Parameters
ADO Open CursorTypes
ADO Open LockTypes
Common ADO Open Options
 ADO Open Method: Parameters
ADO Open CursorTypes
ADO Open LockTypes
Common ADO Open Options


Opening Recordsets  

This is the starting place for both DAO and ADO recordset methods. A local instance of the recordset object must be created with a Dim statement (except with Server coding) for both methods. All the examples below use the object variable "rs" as this instance. The Smith and Sussman textbook uses "rec" but you can use any variable name.

You will have questions about the constants used in the examples, and should consult Access Help (Index: recordset) to learn detailed information about constants (e.g., dbOpenDynaset and adOpenStatic) used in examples below.

Opening in DAO (Workstation)  

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblPeople", dbOpenDynaset)

' Instead of the line above try this SQL

Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)

Opening in ADO (Workstation)  

Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, adOpenStatic

' Instead of the line above try this SQL

rs.Open "SELECT pkPeopleID, LastName FROM tblPeople", CurrentProject.Connection, adOpenStatic

Opening in ADO (Server)  

<body>
<!-- #INCLUDE FILE="./adovbs.inc" -->' You MUST include adovbs.inc for this code to function properly.
<%
' ASP code
Dim ObjConn
Dim rs
Dim strConn
Set ObjConn = Server.CreateObject("ADODB.Connection")
strConn="Microsoft.Jet.OLEDB.4.0;Data Source=" & _ '  permissions are set ONLY for the db folder
    server.mappath("/MIS333K-YourInstructor/FirstName.LastName/db/")&"\sample.mdb;"
ObjConn.Open strConn
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = ObjConn
rs.Open "SELECT * FROM tblSample", ObjConn, adOpenKeyset, adLockPessimistic, adCmdText
' The parameter names in the line above are explained below.
%>
</body>

Parameters of the ADO Open Method  

The ADO Open Method:    recordset.Open Source, ActiveConnection, CursorType, LockType, Options
ParameterDescription
SourceThis is the source of the recordset. The user can input a SQL statement or table name as the record source as long as the input corresponds with the proper option value.
ActiveConnectionThis parameter defines which connection object to use.
CursorTypeThis parameter determines the type of cursor that the server should use when opening the recordset. For more information on CursorTypes, please see the CursorType table below.
LockTypeThis parameter determines what type of locking the server should use when opening the recordset. For more information on LockTypes, please see the LockType table below.
OptionsThis long value is an optional argument that indicates how the server should evaluate the CommandText argument. Some of the common options and their descriptions can be found lower on the page.

ADO Open CursorTypes  

CursorTypeDescription
adOpenDynamicThis CursorType uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed if the server doesn't support them. Bookmarks not supported.
adOpenForwardOnlyThis is the default CursorType. This CursorType uses a forward-only cursor. With this LockType you can only scroll forward through records. This improves performance when you need to make only one pass through a recordset. Bookmarks not supported.
adOpenKeysetThis CursorType uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your recordset. Data changes by other users are still visible.
adOpenStaticThis CursorType uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
adOpenUnspecifiedThis CursorType does not specify the type of cursor.

ADO Open LockTypes  

LockTypeDescription
adLockBatchOptimisticThis LockType indicates optimistic batch updates. This is required for batch update mode.
adLockOptimisticThis LockType indicates optimistic locking, record by record. The server uses optimistic locking, locking records only when you call the Update method.
adLockPessimisticThis LockType indicates pessimistic locking, record by record. The server does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnlyThis is the Default value. This LockType indicates read-only records. You cannot alter the data.
adLockUnspecifiedThis LockType does not specify a type of lock.

Commonly Used ADO Open Options  

OptionDescription
adCmdUnspecifiedDoes not specify the command type argument.
adCmdTextEvaluates CommandText as a textual definition of a command or stored procedure call. You would use this option if you entered the record source as a SQL statement.
adCmdTableEvaluates CommandText as a table name whose columns are all returned by an internally generated SQL query. You would use this option if you entered a table name as the record source.
adCmdTableDirectEvaluates CommandText as a table name whose columns are all returned.. You would use this option if you use an INDEX and open a table as the record source.
adCmdUnknownThis is the Default value. Indicates that the type of command in the CommandText property is not known.

Closing Recordsets (DAO)  

rs.Close
Set rs = Nothing

Closing Recordsets (ADO Workstation and Server)  

rs.Close
Set rs = Nothing
Set objConn = Nothing

Syntax for Fields (Workstation)   

After opening a recordset, you will probably need to refer to its contents. The data fields can be addressed by index number, name, or variable as shown in the five columns below. There is an alternate syntax, shown next, for referencing fields that is faster. 

Dim ID as String
Dim FN as String
Dim SA as String
rs(0)   rs("pkPeopleID")  rs.Fields("pkPeopleID")  rs!pkPeopleID    rs(ID)
rs(1)   rs("FirstName")   rs.Fields("FirstName")   rs!FirstName     rs(FN)
rs(3)   rs("Salary")      rs.Fields("Salary ")     rs!Salary        rs(SA)
This alternate syntax, for DAO and ADO (Workstations only), references fields faster than above. 
Dim ID as Object
Dim LN as Object
' open the DAO or ADO recordsets as shown above (Workstations only)
Set ID = rs("pkPeopleID")' open the object container for these fields
Set LN = rs("LastName")

Do While Not rs.EOF
    Debug.Print ID; LN ' compare this syntax to the usual syntax in the example below
    rs.MoveNext
Loop

Syntax for Fields (Server)  

If opening a recordset through a Server, the only way to refer to fields is through the following method: 

Dim ID, FN, SA
ID = rs("pkPeopleID")
FN = rs("FirstName")
SA = rs("Salary")
Here the value of the fields is put into different variables; using the variables is not necessary, but usually helps with clutter.

Displaying a Recordset (DAO and ADO)  

Do While Not rs.EOF
    Debug.Print rs![pkPeopleID]; rs![LastName]
    rs.MoveNext
Loop

Displaying a Recordset (ADO Server)  

Do Until RsList.EOF

    FirstName = RsList("Firstname")
    Response.Write(FirstName & " <br />")
    RsList.MoveNext

Loop

Editing a Recordset (DAO)  

rs.Edit
rs![LastName] = "Smith-Jones"
rs.Update
' changes to the LastName are lost if record position changes in edit-update

Editing a Recordset (ADO)  

' ADO is naturally in edit mode, so do not use "rs.Edit"
rs![LastName] = "Smith-Jones"
rs.Update

Editing a Recordset (ADO Server)  

' ADO is naturally in edit mode, so do not use "rs.Edit"
rs("LastName") = "Smith-Jones"
rs.Update

Adding New Records (DAO and ADO)  

' A variety of syntax is used below for demonstration 

rs.AddNew ' 
    rs("FirstName") = txtFirst
    rs!Last = txtLast
    rs.Fields("Hire") = Today
    rs(5) = txtStartingSalary
    rs("Sex").Value = cboSex
rs.Update

Adding New Records (ADO Server)  

' A variety of syntax is used below for demonstration 

rs.AddNew ' 
    rs("FirstName") = txtFirst
    rs("LastName") = txtLast
    rs("Hire") = Today
    rs("Salary") = txtStartingSalary
    rs("Sex") = cboSex
rs.Update

Deleting Records  

rs.Delete

Find First Record (DAO)  

rs.MoveFirst
rs.FindFirst "pkPeopleID=2"

If rs.NoMatch = True Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If

Find First Record (ADO)  

rs.MoveFirst

rs.Find "pkPeopleID=2", , adSearchForward

If rs.BOF Or rs.EOF Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If

Find First Record (ADO Server)  

rs.MoveFirst

rs.Find "pkPeopleID=2", , adSearchForward

If rs.BOF Or rs.EOF Then
    Response.Write("Not found")
Else
    Response.Write("Found" & rs("pkPeopleID") & rs("FirstName") & rs("LastName"))
End If

Find Last Record (DAO)  

rs.FindLast "pkPeopleID=2"

If rs.NoMatch = True Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If

Find Last Record (ADO)  

rs.MoveLast

rs.Find "pkPeopleID=2", , adSearchBackward

If rs.BOF Or rs.EOF Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If

Find Last Record (ADO Server)  

rs.MoveLast

rs.Find "pkPeopleID=2", , adSearchBackward

If rs.BOF Or rs.EOF Then
    Response.Write("Not found")
Else
    Respones.Write("Found" & rs("pkPeopleID") & rs("FirstName") & rs("LastName"))
End If

More Syntax for Finding Records (DAO and ADO)  

The find syntax used above is "non-indexed" and is performed with constants. The examples are repeated here and followed by find syntax with variables. 

rs.FindLast "pkPeopleID=2"          ' numeric constant - DAO
rs.Find "pkPeopleID=2", , adSearchForward   ' numeric constant - ADO

rs.FindLast "LastName='Jones'"          ' string constant - DAO
rs.Find "LastName='Jones'", , adSearchForward   ' string constant - ADO
Of course you will rarely write programs that use only constants. Most of the time your programs will use variables instead. Here are examples of numeric, string and date variables. These examples are for non-indexed finds. 
Dim intTemp As Integer
Dim strTemp As String
Dim dteTemp As Integer

' DAO syntax examples
rs.FindFirst "pkPeopleID=" & intTemp
rs.FindFirst "LastName=" & "'" & strTemp & "'"
rs.FindFirst "Hire=" & "#" & dteTemp & "#"

' ADO syntax examples
rs.Find "pkPeopleID=" & intTemp, , adSearchForward
rs.Find "LastName=" & "'" & strTemp & "'", , adSearchForward
rs.Find "Hire=" & "#" & dteTemp & "#", , adSearchForward

Recordset to Array (DAO and ADO)  

Access provides a means of converting table data into two-dimensional arrays by using recordset programming. You can have all records or a few records placed into an array. Information on arrays is available elsewhere. For example, you could get the 10 earliest hires (names only) from tblPeople into an array called StaffNames(1,9) in this way: 

'2 columns and 10 rows into StaffNames(1,9)

MySQL = 'SELECT FirstName, LastName FROM tblPeople ORDER BY DateValue(Hire)'


' Open the recordset in DAO or ADO (see above)

StaffNames = rs.GetRows(10)
rs.Close
Set rs = Nothing

Execute SQL  

You sometimes can choose between two methods of performing the same table manipulations: recordset programming or action queries (SQL). Although the best-practice method is often unclear to students, in larger commercial systems action queries would be preferred to recordsets because of automatic optimization of all SQL processing in those systems.

Recordset programming provides a more visible step by step procedure-based method for performing these same tasks, and in fact a combination of the two methods can be highly efficient, too. The examples that follow next do not show opening or closing recordsets since that is unnecessary for action queries.

There is also a small collection of SQL examples where you can see a wider variety of syntax. And there is an example below of combining SQL and recordsets.

Execute SQL (DAO)  

Dim MySQL as String
MySQL = "UPDATE tblPeople SET IsSelected = No;"
CurrentDB.Execute MySQL ' see a 1-line version of this below

' Some examples of 1-line SQL programs (also see ADO below)

CurrentDB.Execute "UPDATE tblPeople SET IsSelected = No;"
CurrentDB.Execute "UPDATE tblPeople SET Salary = Salary*1.03 WHERE Sex='F';"
CurrentDB.Execute "UPDATE tblPeople SET LastName = 'Smith-Jones' WHERE FirstName = 'Mary' AND LastName = 'Jones';"  ' note: this statement works only for DAO
CurrentDB.Execute "INSERT INTO tblPeople (pkPeopleID,LastName,FirstName,Hire, Salary,Sex) VALUES (101,'Smith-Kline','Mary',#11/01/00#,50000,'F')"

' 1-line programs to copy a table, and then change its structure

CurrentDB.Execute "SELECT tblPeople.* INTO tblPeopleCopy FROM tblPeople;"

CurrentDB.Execute "ALTER TABLE tblPeopleCopy ADD COLUMN SpouseName TEXT;"

Execute SQL (ADO)  

Dim MySQL as String
MySQL = "UPDATE tblPeople SET IsSelected = Yes;"
CurrentProject.Connection.Execute MySQL

' Some examples of SQL statements (also see DAO above)

CurrentProject.Connection.Execute "UPDATE tblPeople SET IsSelected = Yes;"
CurrentProject.Connection.Execute "UPDATE tblPeople SET Salary = Salary*1.02 WHERE Sex='M';"

' Alternative ADO syntax

Dim cd As New ADODB.Command
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cd.ActiveConnection = cn
cd.CommandText = MySQL
cd.Execute

Execute SQL (ADO Server)  

The following are examples of action queries being used through ASP. 

strSQL = "INSERT INTO tblPeople (FirstName, LastName) VALUES ('Nick','Jones');"

'  ObjConn must be set up as seen in previous examples
SET RsList = ObjConn.Execute(strSQL)
'Here the SQL used is an action query that will
'add a new record (in this case with the name /Nick Jones/ but this
'information is obviously just for the example). Other action SQLs can
'be used that either update or 'delete data

"DELETE FROM tblSample WHERE FirstName = 'Nick' AND LastName = 'Jones';"

"UPDATE tblSample SET FirstName = 'Nicholas' WHERE FirstName = 'Nick';"

Filtering Records (ADO Workstation and Server)  

The records used can be filtered through recordset manipulation also. 

'Here a filter is placed on the recordset to only show records with FirstName Harry
'It is important that the following line be put before the recordset is opened
RsList.Filter = "Firstname = 'Harry'"

RsList.Open

Do Until RsList.EOF

    FirstName = RsList("Firstname")
    Response.Write(FirstName & " < br />")
    RsList.MoveNext

Loop

RsList.Close
Something that is very useful with the filter property is that along with the usual operators (<, >, <=, >=, <>, =) the operator LIKE can also be used; when comparing strings the LIKE operator allows the use of wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last or first character in the string. 
RsList.Filter = "Firstname LIKE 'H*'"

RsList.Open

Do Until RsList.EOF

    FirstName = RsList("Firstname")
    Response.Write(FirstName & " < br />")
    RsList.MoveNext

Loop

RsList.Close
Here the records shown will be ones with FirstName beginning in H.

Combining SQL and Recordsets (DAO or ADO)  

If your programming objectives cannot be distilled into a single SQL statement, and if you are inclined to use step-by-step programming methods, consider blending SQL and recordset programming. Use a reduced SQL statement to perform the initial part of the job. Finish it with recordsets. Here is an example.

Find the hire dates and current salaries of the male staff who have the smallest and largest paychecks. This job entails sequencing the male records by salary (use SQL) and finding the record at the top and bottom of the salaries (use recordsets). Here is a skeleton program for either DAO or ADO. 

MySQL = "SELECT Hire, Salary, Sex FROM tblPeople WHERE Sex='M' ORDER BY Salary;"
' open the DAO or ADO recordset (use example code provided elsewhere)
rs.MoveFirst
Debug.Print "Lowest paid man earns "; rs!Salary; " and was hired "; rs!Hire
rs.MoveLast
Debug.Print "Highest paid man earns "; rs!Salary; " and was hired "; rs!Hire
' close the DAO or ADO recordset(use example code provided elsewhere)
General Objective: for any recordset programming problem you should use SQL to open the minimum number of records and fields in the best sequence to accomplish the job. Your program will run quickest with the smallest amount of data, and the fewest number of steps. And the smaller the program the less chance for bugs.

Combining SQL and Recordsets (Server)  

If your programming objectives cannot be distilled into a single SQL statement, and if you are inclined to use step-by-step programming methods, consider blending SQL and recordset programming. Use a reduced SQL statement to perform the initial part of the job. Finish it with recordsets. Here is an example.

Find the hire dates and current salaries of the male staff who have the smallest and largest paychecks. This job entails sequencing the male records by salary (use SQL) and finding the record at the top and bottom of the salaries (use recordsets). Here is a skeleton program for either DAO or ADO. 

MySQL = "SELECT Hire, Salary, Sex FROM tblPeople WHERE Sex='M' ORDER BY Salary;"
' open the recordset (use example code provided elsewhere)
rs.MoveFirst
Response.Write("Lowest paid man earns " & rs("Salary") & " and was hired " & rs("Hire"))
rs.MoveLast
Response.Write("Highest paid man earns " & rs("Salary") & " and was hired " & rs("Hire"))
' close the recordset(use example code provided elsewhere)
General Objective: for any recordset programming problem you should use SQL to open the minimum number of records and fields in the best sequence to accomplish the job. Your program will run quickest with the smallest amount of data, and the fewest number of steps. And the smaller the program the less chance for bugs.

Top of page