Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Using DAO Recordsets

DAO Recordsets to Modify, Add, and Delete Rows

This article describes how to create code in VBA to utilize the DAO objects in MS Access to automate Insert, Update and Delete processes on a table.

Looping through an MS Access DAO Recordset

The following demonstrates how to open a query as the recordsource and loop through it and print the data to the immediate window.

Create a query with some data. In this case, a table with salespeople and their sales figures will be used.

Query for Recordset

Next, construct code in a VBA Module to generate the recordset with DAO objects and loop through it.

Public Sub daoRecordset()
 Dim db As DAO.Database
 Dim qdf As DAO.QueryDef
 Dim rs As DAO.Recordset
 Dim strValue As String

 Set db = CurrentDb
 Set qdf = db.QueryDefs("qrySalesPeople")

 Set rs = qdf.OpenRecordset

 If Not (rs.EOF And rs.BOF) Then
  Do While Not rs.EOF
   strValue = rs.Fields(0).Value & ", " & rs.Fields(1).Value & ", " &     Format(rs.Fields(2).Value, "Currency")
   strValue = Replace(strValue, Chr(13), "")
   Debug.Print strValue
   rs.MoveNext

  Loop

  rs.Close
  Set rs = Nothing
  Set qdf = Nothing
  Set db = Nothing

 End If
End Sub

The results from the Immediate Window appear as follows.

DAO Recordset Results in Immediate Window

Update Tables with DAO Recordsets

This example uses a table called tblSalesPeople with IDs, Sales Names, SalesYTD, and Rating fields. The Rating field will be updated for each sales person based on their SalesYTD value.

SalesPerson table for DAO Update

The following code uses the DAO database and Recordset objects to open the table, evaluate the SalesYTD field using a Select Case statement and then updating the Rating field with the value set in the variable strRating.

Public Sub daoUpdateRecordset()
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim crValue As Currency, strRating As String

 'set the DAO database to current Access db
 Set db = CurrentDb

 'open the table as the recordset
 Set rs = db.OpenRecordset("tblSalesPeople", dbOpenTable)

 'test to make sure there are rows
 If Not (rs.EOF And rs.BOF) Then
 'loop through the records
  Do While Not rs.EOF
  'get the SalesYTD field to evaluate
   crValue = rs.Fields(2).Value

  'provide a Rating based on the SalesYTD
  Select Case (crValue)
   Case Is > 1000000
    strRating = "Great"
   Case Is < 1000000
    strRating = "Average"
   Case Else
    strRating = "None"
  End Select

  'set the Recordset up for Editing and update the value in the table
   rs.Edit
   rs.Fields(3).Value = strRating
   rs.Update

   rs.MoveNext

  Loop

  'cleanup work
  rs.Close
  Set rs = Nothing
  Set db = Nothing

 End If

End Sub

Verify that the table has been updated with the correct values.

SalesPerson table After DAO Update

Delete Rows from Tables with DAO Recordsets

This example uses the tblSalesPeople table again and will delete rows based on the sales person's SalesYTD value.

Public Sub daoDeleteRows()
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim crValue As Currency, strRating As String

 'set the DAO database to current Access db
 Set db = CurrentDb

 'open the table as the recordset
 Set rs = db.OpenRecordset("tblSalesPeople", dbOpenTable)
 'test to make sure there are rows
 If Not (rs.EOF And rs.BOF) Then

 'loop through the records
  Do While Not rs.EOF
  'get the SalesYTD field to evaluate
  crValue = rs.Fields(2).Value

   'Delete the row based on the SalesYTD
   If (crValue < 1000000) Then
    rs.Delete
   End If

   rs.MoveNext

  Loop

  'cleanup work
  rs.Close

  Set rs = Nothing
  Set db = Nothing
 End If
End Sub

Verify that the rows have been deleted from the table.

Deleted Rows from Table with DAO

Insert Rows Tables with DAO Recordsets

This example uses the tblSalesPeople table again and will add rows using DAO.

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

 'set the DAO database to current Access db
 Set db = CurrentDb

 'open the table as the recordset
 Set rs = db.OpenRecordset("tblSalesPeople", dbOpenTable)

 'add the row
 With rs
  .AddNew
  !SalesPersonID = 999
  !SalesName = "John Doe"
  !SalesYTD = 700000000.99
  !Rating = "Great"
 End With

 rs.Update

 'cleanup work
 rs.Close
 Set rs = Nothing
 Set db = Nothing
End Sub

Verify that the row has been added to the database table.

Inserted Row Using DAO

DAO and MS Access VBA can automate processes that need to add, modify or delete rows in tables.