Making Bulk Changes

Many of the changes you may otherwise perform by looping through all the records in a Recordset object can be done more efficiently with an update or delete query. For example, the following code shows how to update employee records to change the Title field value for all sales representatives to “Account Executive”. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, qdf As QueryDef

Set dbs = OpenDatabase(strDbPath)
' Create temporary QueryDef object.
Set qdf = dbs.CreateQueryDef("")

With qdf
	.SQL = "UPDATE Employees " & _
		"SET Employees.Title = ""Account Executive"" " & _
		"WHERE Employees.Title = ""Sales Representative"";"
	' Invoke query.
	.Execute dbFailOnError
End With

Of course, the entire SQL string in this example can be replaced with a stored or temporary parameter query, in which case the program would prompt the user for parameter values. The following example shows how the previous example might be rewritten as a parameter query. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, qdf As QueryDef
Dim strSQLUpdate As String, strOld As String, strNew As String

Set dbs = OpenDatabase(strDbPath)
strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " & _
	"UPDATE Employees " & _
	"SET Employees.Title = [New Title] " & _
	"WHERE Employees.Title = [Old Title]; "

' Create the QueryDef object.
Set qdf = dbs.CreateQueryDef("", strSQLUpdate)
' Prompt for old title.
strOld = InputBox("Enter old job title")
If Len(strOld) = 0 Then Exit Sub
' Prompt for new title.
strNew = InputBox("Enter new job title")
If Len(strNew) = 0 Then Exit Sub
With qdf
	' Set parameter values.
	.Parameters("Old Title") = strOld
	.Parameters("New Title") = strNew
	' Invoke query.
	.Execute
End With