Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 36 ยท Macros and VBA

DAO Recordsets and Error Handling

Read and change Access data from VBA with DAO recordsets. Learn OpenRecordset, looping, AddNew and Edit, transactions, On Error, Err and a reusable error handler.

A recordset is a cursor over rows: VBA opens a table or query and then walks through the records one by one, reading or changing fields. Access has two data-access libraries: DAO (Data Access Objects), designed for the Access database engine and the one you should use for Access data, and ADO, which suits other sources. This lesson uses DAO. You also learn how to handle runtime errors cleanly, because code that talks to data fails in predictable ways: a record is locked, a table is missing, a value is Null.

Before you reach for a recordset, ask whether one SQL statement could do the job. A single UPDATE is far faster than looping through rows. Use recordsets when each row needs different logic, or when you must call other code per row.

Opening a recordset

Add a reference to the DAO library (Tools > References > Microsoft Office xx.0 Access database engine Object Library; it is usually there already). Then:

VBA
Public Sub ListCustomers()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT CustomerID, Name, Credit FROM Customers " & _
                              "WHERE Active = True ORDER BY Name", dbOpenSnapshot)

    Do While Not rs.EOF
        Debug.Print rs!CustomerID, rs!Name, Nz(rs!Credit, 0)
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Key points:

  • Types: dbOpenSnapshot is a read-only copy (fastest for reading), dbOpenDynaset is updatable (the default for tables and updatable queries), dbOpenTable opens a local table directly.
  • rs.EOF is true after the last record, rs.BOF before the first. Always test Not rs.EOF first, because an empty recordset starts at EOF; an unguarded rs!Name then raises error 3021 "No current record".
  • Always close recordsets and release objects with Set ... = Nothing so locks are freed.
  • rs.RecordCount is only reliable after rs.MoveLast; for a simple count, use DCount or SELECT COUNT(*).

The loop above is the same as this pattern, here run on rows with a JavaScript stand-in so you can see the cursor idea (move, test the end, read the fields):

JS Stand-in for a recordset loop

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Finding, adding, editing and deleting

VBA
' Find one record
rs.FindFirst "CustomerID = " & lngId
If rs.NoMatch Then
    MsgBox "Not found"
Else
    Debug.Print rs!Name
End If

' Add
rs.AddNew
rs!Name = "New Customer"
rs!Credit = 0
rs.Update              ' saves; without Update the new row is discarded

' Edit
rs.Edit
rs!Credit = rs!Credit + 25
rs.Update

' Delete the current record
rs.Delete

Edit or AddNew must be followed by Update (or CancelUpdate). FindFirst works on dynasets and snapshots; for a table-type recordset use Seek on an index. On large tables, filtering in the SQL string (WHERE) is much faster than FindFirst. After AddNew and Update you can read the new AutoNumber key with rs.Bookmark = rs.LastModified followed by rs!ID.

The set-based alternative to an Edit loop is one statement, which you can try here:

SQL One UPDATE instead of a loop

Press Run (or Ctrl + Enter) to see the result here.

Transactions

A transaction makes several changes succeed or fail together. DAO transactions are on the workspace:

VBA
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)

ws.BeginTrans
On Error GoTo Failed
CurrentDb.Execute "UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1", dbFailOnError
CurrentDb.Execute "UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2", dbFailOnError
ws.CommitTrans
Exit Sub

Failed:
ws.Rollback
MsgBox "Transfer cancelled: " & Err.Description

Transactions also speed up bulk writes noticeably, because Access flushes to disk once at CommitTrans instead of after every record.

Error handling with On Error

By default an unhandled runtime error pops a dialog and may stop the program. Use On Error GoTo label to handle it yourself. The standard shape of a procedure:

VBA
Public Sub SafeProcedure()
    Dim rs As DAO.Recordset
    On Error GoTo ErrHandler

    Set rs = CurrentDb.OpenRecordset("Customers", dbOpenDynaset)
    ' ... work ...

CleanExit:
    On Error Resume Next        ' don't fail while cleaning up
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "SafeProcedure"
    Resume CleanExit
End Sub

The Err object holds the number, description and source of the last error. Resume Next continues at the line after the failing one, Resume retries the failing line and Resume label jumps somewhere safe. On Error Resume Next alone is dangerous: it hides every error. Use it only for a single line you expect might fail (and then check Err.Number), and reset with On Error GoTo 0.

Common errors worth recognizing: 3021 (no current record), 3022 (duplicate key), 3200 (a related record exists, so the row cannot be deleted), 3314 (a required field is Null), 3061 (too few parameters, usually a misspelled field name), and 2501 (an action was cancelled). You can raise your own with Err.Raise vbObjectError + 1000, "Module", "Message".

A central handler that logs errors to a table is a hallmark of a mature application. The pattern, with a SQL table receiving the log rows:

SQL An error log table

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why must you test Not rs.EOF before reading fields?

    With no rows the cursor starts at EOF; reading rs!Field then raises error 3021.
  2. 2. What must follow rs.Edit or rs.AddNew to save changes?

    Changes stay in a buffer until rs.Update is called; moving away without it discards them.
  3. 3. When is one SQL UPDATE better than a recordset loop?

    Set-based SQL is much faster and simpler when the change is uniform.
  4. 4. What is wrong with a bare On Error Resume Next at the top of a procedure?

    It tells VBA to ignore errors, so bugs and failed saves pass silently.

Summary

  • Use DAO: CurrentDb.OpenRecordset, loop with Do While Not rs.EOF ... rs.MoveNext, close and release.
  • AddNew/Edit need Update; use FindFirst sparingly and prefer filtering in SQL.
  • Prefer set-based SQL over loops; wrap multi-step changes in BeginTrans/CommitTrans/Rollback.
  • Handle errors with On Error GoTo, a clean-exit label and Err.Number/Err.Description; never leave a blanket Resume Next.

Next: Importing data into Access