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