HandsOn Executing an Update Query

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Execute_UpdateQuery procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Execute_UpdateQuery()

Dim conn As ADODB.Connection Dim NumOfRec As Integer Dim strPath As String strPath = CurrentProject.Path & "\Northwind.mdb"

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath conn.Execute "UPDATE Products " & _ "SET UnitPrice = UnitPrice + 1" & _ "WHERE CategoryId =8", _ NumOfRec, adExecuteNoRecords

MsgBox NumOfRec & " records were updated." conn.Close Set conn = Nothing End Sub

The above procedure uses the Data Manipulation Language (DML) UPDATE statement to make a change in the UnitPrice field of the Products table. The Execute method of the Connection object allows the provider to return the number of records that were affected via the RecordsAffected parameter. This parameter applies only for Action queries or stored procedures. To get the number of records returned by a result-returning query or stored procedure, you must use the RecordCount property. In the procedure shown above, we store the number of records affected in the string variable NumOfRec.

Note that when a command does not return a recordset, you should include the value adExecuteNoRecords. The adExecuteNoRecords value can only be passed as an optional parameter to the Command or Connection Execute method.

The procedure in Hands-On 15-9 demonstrates how to execute an update query by using the ADO Command object instead of the Connection object used in the preceding example. By running the following example, the UnitPrice of all the records in the Products table will be increased by 10 percent.

Creating and Running Queries with ADO | 283 Creating and Manipulating Databases with ADO

0 0

Post a comment