UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Recordsets for Beginners    
Recordsets for Beginners

Related Content:
    BookmarkProperty


Contents

SQL vs. Recordsets


Before getting started, it is well to note that in the majority of cases, we are far better off using an SQL solution as opposed to a VBA Recordset solution. SQL executes expotentially faster than a VBA recordset, is also far more flexible, especially when working with nested sets of data. Working with nested data among several recordsets is almost always a severely performance degrading operation.

Many times, we find that it's the case that SQL would have served our needs better and more efficiently than a VBA recordset. This is not to say there are never cases where we do need to use VBA, but rather it's a matter of finding the right tool and in most cases SQL tends to be the right tool.

There's also happy middle ground such as using VBA to build dynamic SQL that would not be otherwise possible or practical with built-in queries that would be faster than looping through a recordset.

ADO vs. DAO


There are two types of recordsets. ADO (Active Data Object) and DAO (Data Access Object), and each behave and perform differently. Without getting into too much detail, it will suffice to say that if you are working in Access and only Access, you likely want the DAO model. But depending on the version of Access, the ADO Reference may be set before the DAO reference, in which case a recordset variable will be of the ADO model. It is always best to disambiguate (explicitly tell which one):

CODE
Dim rst As DAO.Recordset


For further information on DAO vs. ADO, see Choosing between DAO and ADO

From here on out this article will refer to DAO.

Setting and Cleaning up a Recordset Object


To set a recordset object, we use the OpenRecordset method of a database object. There are a few ways to do this:

CODE

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("DataSource")

CODE

Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("DataSource")



Data Sources with a Recordset

The "DataSource" referred to above can be one of three things, passed as a literal string or a variable:

  • a Table Name
  • a Query Name
  • an SQL string that returns records (not an Action Query)



Cleaning Up a Recordset Object


Be very careful about how the objects are cleaned up. The rule is "Close what you have opened and destroy what you have created".

CODE

rs.Close
Set rs = Nothing
Set db = Nothing


These closing lines are very important and MUST be run (we include them in an exit procedure in case we have an error to ensure that the recordset is always closed and the object is dereferenced). Failure to do so may induce some very hard to track bugs.

With a recordset, we need to explicitly open it, and therefore it needs to be explicitly closed before the object pointer is destroyed.

If you used an object variable for the Database, you should also set this to Nothing (but, as it did not need to be "opened", we need not "close" it).

VBA is supposed to automatically do this for us in case we don't get it ourselves, but it doesn't always catch it, and wierd things start happening when they are not caught.


Cursors and Postion


We now need to know something about recordset cursors and their position. A recordset has records as follows:

BOF
Record1
Record2
EOF


BOF (beginning of file) and EOF (end of file) are always there. Records may not be. So, the first thing to do when opening a recordset is generally to check and make sure there are records in it. IF there are records, the RecordCount property of the recordset will return a nonzero value (not always the number of records though). Or, you can determine this by checking the BOF and EOF properties... if they are both true, there are no records.

CODE

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SomeTable")
If rs.Recordcount <> 0 Then
 'there are records
End If

'or, functionally the same...

If (rs.EOF = False) OR (rs.BOF = False) Then
 'there are recordset
End If

rs.Close
Set rs = Nothing


If we are planning to work with existing records, this is a fundemental concept that needs to be applied to avoid cursor move errors.

The Recordcount Property


We mentioned earlier that the Recordcount property of a DAO Recordset object may return a nonzero number if there are records in the set. For performance reasons, there are times when upon opening a recordset, the full count may not be read until required because it would facilitate a move through all records in the set. When VBA waits for us to go there ourselves, performance is greatly increased for us.

To get an accurate count of records, we must explicitly move the the last record ( .MoveLast) before checking the RecordCount property of a recordset.

Moving the Cursor


To move the cursor, employ one of the following methods of the Recordset object:

rs.MoveFirst
rs.MoveLast
rs.MoveNext
rs.MovePrevious
rs.Move


You can move from the last record into EOF, you can move from the first record into BOF, but you cannot move "past" either one. By default, a recordset is opened with the cursor on the first record if there are records in the set. It is a good idea to check the BOF or EOF properties before navigating towards either one. The following would produce applicable errors:

CODE

Set rs = CurrentDb.OpenRecordset("SomeTable")
rs.MovePrevious     'on BOF
Debug.Print rs.BOF
rs.MovePrevious     'error
rs.MoveLast
rs.MoveNext           'on EOF
Debug.Print rs.EOF
rs.MoveNext           'error


This is why verifing a recordcount before navigation is important.

Finding a Record


One more method of naviagation is the FindFirst/NoMatch method...

CODE

With rs
 .FindFirst "Field1 = " & Value1
 If Not .NoMatch Then

   'the record was found and is now the current cursor position

End With



Referencing a Recordset Field


The fields available to us in the recordset is based on the table or query that is used as a datasource, and are ordered in the recordset the same as they are ordered in the table or query.

We can reference the field to read or write a value to it by calling the Fields collection of the Recordset object. There are a number of ways to do this:

CODE

rs.Fields("FieldName")
rs.Fields(VariableName)
rs.Fields(#)
rs(#)


A field name can be referenced as a literal string, a string variable, a numeric index (or a number variable containing the numeric index). In the example above, we replace # with the index of the field that we wish to reference. This is a 0 based index... the first field in the table/query is referenced as 0. In the following example, we use a numeric index value to print the value of Field3 in the first record of the set

CODE

Dim rs As DAO.Recordset
Dim strSQL As String
Dim intFieldIndex As Integer

strSQL = "SELECT Field1, Field2, Field3 FROM TableName"
intFieldIndex = 2
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.Recordcount <> 0 Then
 Debug.Print rs(intFieldIndex)
 Debug.Print rs.Fields(2)
End If
rs.Close
Set rs = Nothing


While it is possible to use a numeric index for referencing a field, it is generally desired to instead use a literal string or variable containing the field name. If the order of fields for any reason happens to change in your table or query structure, using a numeric index will yield incorrect results, where if we instead reference the field name itself, it will not matter what order the fields are in, and the code will not be broken.

To enumerate all fields for a given recordset, we can use a For Each...Next loop across the DAO Field object as shown:

CODE
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set rs = CurrentDb.OpenRecordset([...])

For Each fld In rs.Fields
  Debug.Print fld.Name &
Next

rs.Close
Set rs = Nothing



Adding a Record

To add a new record, use the following:

CODE

With rs
 .AddNew
   .Fields("FieldName1") = Value1
   .Fields("FieldName2") = Value2
 .Update
End With



Editing a Record


To edit a previously existing record, do this (after navigating to an existing record of choice):

CODE

With rs
 .Edit
   .Fields("Field1") = Value1
   .Fields("Field2") = Value2
 .Update
End With



Looping a Recordset


Here's an example to demonstrate how to loop all records of a recordset:

CODE

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SomeTable")
If rs.Recordcount <> 0 Then
 rs.MoveFirst
 While Not rs.EOF

   'do stuff here

   rs.MoveNext
 Wend
End If
rs.Close
Set rs = Nothing



Puttting it all together


So, a complete code snippet to navigate to a record and edit it might look like this (make note of the error handler and exit procedure to verify that the recordset is closed and uninstantiated):

CODE

Sub DoSomething()
On Error Goto Err_Proc

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SomeTable")
If rs.Recordcount <> 0 Then
 With rs
   .FindFirst "Field1" = Me.TextBox1
   If Not .NoMatch Then
     .Edit
       .Fields("Field2") = Me.Textbox2
     .Update
   End If
 End With
End If

Exit_Proc:
 On Error Resume Next
 rs.Close
 Set rs = Nothing
 Exit Sub
Err_Proc:
 MsgBox Err.Number & " " & Err.Description
 Resume Exit_Proc
End Sub



Edit Discussion
Thank you for your support!
This page has been accessed 88,197 times.  This page was last modified 17:03, 11 February 2012 by Jack Leach. Contributions by BananaRepublic  Disclaimers