Table of contents
Collapse the table of content
Expand the table of content

Make Bulk Changes to a DAO Recordset

office 365 dev account|Last Updated: 6/12/2017
1 Contributor

After you have created a table-type or dynaset-type Recordset object, you can change, delete, or add new records. You cannot change, delete, or add records to a snapshot-type or forward-only-type Recordset object.

Many of the changes you may otherwise perform in a loop can be done more efficiently with an update or delete query using SQL. The following example creates a QueryDef object to update the Employees table and then runs the query.

Dim dbsNorthwind As DAO.Database 
Dim qdfChangeTitles As DAO.QueryDef 

   Set dbsNorthwind = CurrentDb 
   Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("") 

   qdfChangeTitles.SQL = "UPDATE Employees SET Title = " &; _ 
                         "'Account Executive' WHERE Title = " &; _ 
                         "'Sales Representative'" 

   ' Invoke query. 
   qdfChangeTitles.Execute dbFailOnError 

You can replace the entire SQL string in this example with a stored parameter query, in which case the procedure would prompt the user for parameter values. The following example shows how the previous example may be rewritten as a stored parameter query.

Dim dbsNorthwind As DAO.Database 
Dim qdfChangeTitles As DAO.QueryDef 
Dim strSQLUpdate As String 
Dim strOld As String 
Dim strNew As String 

   Set dbsNorthwind = CurrentDb 

   strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " &; _ 
                  "UPDATE Employees SET Title = [New Title] WHERE " &; _ 
                  "Title = [Old Title]" 

   ' Create the unstored QueryDef object. 
   Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("", strSQLUpdate) 

   ' Prompt for old title. 
   strOld = InputBox("Enter old job title:") 

   ' Prompt for new title. 
   strNew = InputBox("Enter new job title:") 

   ' Set parameters. 
   qdfChangeTitles.Parameters("Old Title") = strOld 
   qdfChangeTitles.Parameters("New Title") = strNew 

   ' Invoke query. 

Note A delete query is much more efficient than code that loops through a Recordset, modifying or deleting one record at a time.

© 2018 Microsoft