Microsoft Access Tips for Serious Users

Provided by Allen Browne, allen@allenbrowne.com. Updated September 2007.

VBA Traps: Working with Recordsets

This article highlights ten common traps with DAO recordsets in VBA code.

Most of the traps yield no syntax error; they are bugs lying dormant in your code until particular conditions are met. Your program then fails, or returns inaccurate results.

1. DAO verses ADO

The DAO and ADO libraries both have a Recordset object, but with different methods, properties, and options.

DAO is the native Access library (what Access itself uses), whereas ADO is a more generic library (now superseded by the vastly different ADO.NET library.)

Different versions of Access default to different libraries. See Solving Problems with Library References for details.

This article assumes DAO recordsets.

Solution:

To ensure your code works reliably:

Set your references to use just the library you want.
If you must use both, list your main one first.
Disambiguate by specifying which library's recordset you want. Use:
Dim rs As DAO.Recordset
not:
Dim rs As Recordset

2. Recordset types

There are different types of DAO recordset, with different methods.

When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.)

The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.

Solution:

Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:

    Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

3. Move with no records

Using any of the Move methods (MoveFirst, MoveLast, MoveNext, or MovePrevious) causes an error if the recordset has no records.

Solution:

Test before using any of the Move methods. Either of these approaches works:

    If Not (rs.BOF And rs.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true.
   If rs.RecordCount <> 0 Then     '100% reliable in DAO, but some ADO recordsets return -1 as the count.

4. RecordCount without MoveLast

For recordsets based on queries, SQL statements, and attached tables, the RecordCount property returns the number of records accessed so far. When you first OpenRecordset(), Access grabs the first record, and keeps processing your code while the others load. So, if you test RecordCount immediately after you OpenRecordset, you typically get 0 (if there are no records), or 1 (if there are any, regardless of how many will load.) This does not apply to recordsets of type dbOpenTable type (the default for local tables.)

Solution:

If you need to know the RecordCount, use the MoveLast method first. This forces Access to wait while all records load, so the RecordCount reflects the entire recordset.

Hint:

Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist, so there is no need to MoveLast if you only want to know if you have records to work with.

5. MoveNext without testing EOF

A MoveNext may take you to the end of the recordset (EOF) or a MovePrevious to the beginning of the recordset (BOF). Failure to test for these conditions means your code works for most cases, but generates an error one day when the last/first record is accessed.

The problem is prevalent when you have another exit condition in mind for your loop, so you are not thinking of EOF. Test for EOF (or BOF if moving backwards) before checking the real exit condition for your loop.

Solution:

Use this construct for looping through Access recordsets:

    Do while Not rst.EOF
       If rst![MyField] <> Something Then  'The real loop exit condition.
           Exit Do
       End If
        ' Rest of your code here.
       rst.MoveNext
   Loop

6. Loop without MoveNext

When looping through records, it is easy to create an endless loop by omitting the line rst.MoveNext. Your code then becomes stuck in an endless loop.

Solution:

VBA is more forgiving than most other languages: just press Ctrl+Break to break out of the loop.

Hint:

Even in a quick'n'dirty procedure, a progress indicator lets you know if a loop is stuck. The simplest reports every 100th record in the loop like this:

    i = i + 1
   If i Mod 100 = 0 Then    'Mod gives the remainder of i divided by 100.
       DoCmd.Echo True, i
   End If

7. FindFirst/Seek without testing NoMatch

If you use Seek or one of the Find methods (FindFirst, FindLast, FindNext, FindPrevious), and do not test NoMatch, your code will appear to work until you strike a case where the find failed.

Solution:

Always test for NoMatch after using Seek or a Find method. Example:

    rs.FindFirst "City = ""New York"""
   If Not rs.NoMatch Then
        'It's okay to keep processing.
   End If

8. AddNew without moving to LastModified

When inserting a new record into a recordset, the new record does not automatically become the current record.

Solution:

To access the new record, move to the bookmark LastModified:

    rst.AddNew
        'Assign fields here.
   rst.Update
   rst.Bookmark = rst.LastModified
   ' Work with the new record here.

9. Failing to close a recordset

It is poor programming to open anything without explicitly closing it. This problem is particularly acute in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.

Solution:

Always close recordsets and set objects to Nothing in the error recovery of your procedure. Use this construct:

    Sub MyProc
   On Error Goto Err_MyProc
       Dim db as Database
       Dim rst As Recordset

       Set db = CurrentDb()
       Set rst = db.OpenRecordset("MyTable")
        'Useful code here.
       rst.Close            'Close what you opened.

   Exit_MyProc:
       Set rst = Nothing    'Deassign all objects.
       Set db = Nothing
       Exit Sub

   Err_MyProc:
        'Error handler here.
       Resume Exit_MyProc
   End Sub

10. Nested recordsets

Access 2007 introduced the possibility that a field in a recordset may itself be a recordset. This applies to complex data types - multi-value fields and attachments - in an ACCDB, not MDB.

Any code that examines the Fields of a recordset or applies criteria is affected. Test the field's Type, and introduce another loop to walk the fields within the recordset that is a field in your main recordset. For a list of the field types, see Field type reference.

Many professional developers avoid the complex data types, believing they introduce more problems than they solve. The hidden structure makes it harder to manage them, harder to apply criteria, harder to pass arguments, harder to determine the delimiters to use for a field, and harder to upsize since other databases like SQL Server don't use these complex types. Eschewing the complex data types is a perfectly value choice if you only deal with databases you created, but if you support end users or write generic utilities to work with any Access tables, you must learn to handle them.

Solutions

Specifically test for and handle the complex data types if your code must work with databases in Access 2007 or later. Particularly, if you:

Loop through the fields of a recordset, examining each.
Apply criteria to a field without knowing its data type.

The Find-as-you-type utility contains a practical example of testing for and excluding the complex data types in the function FindAsUTypeLoad(), while ensuring the code still works in earlier versions of Access.

Home

Index of tips

Top

Smart Computing 123

Pages

Tuesday, February 24, 2009