Microsoft Office 2000/Visual Basic Programmer's Guide

Performing Bulk Operations

The Execute method of the ADO Connection object can be used to run queries that return records, as well as queries that issue SQL statements to perform actions such as updating or deleting multiple records, or that create a table from records read from other tables. These kinds of queries are often called bulk operations or action queries. The following code example demonstrates how to execute a bulk operation in ADO.

Sub RunBulkOpQuery(strDBPath As String, _
                    strSQL As String)
   Dim cnn          As ADODB.Connection
   Dim lngAffected  As Long
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Execute the query.
   cnn.Execute CommandText:=strSQL, _
               RecordsAffected:=lngAffected, _
               Options:=adExecuteNoRecords

   Debug.Print "Records Affected = " & lngAffected
   
   ' Close connection and destroy object variables.
   cnn.Close
   Set cnn = Nothing
End Sub

For example, to use this procedure to update records in the Customers table in the Northwind database that have "USA" in the Country field to read "United States," you can use a line of code like this:

RunBulkOpQuery "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "UPDATE Customers SET Country = 'United States'" _
   & "WHERE Country = 'USA'"

The RunBulkOpQuery procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.