Listing 6: Using the Recordset object to update data



' Declare connection and connection string variables
Dim conSqlServer, csSqlServer

' Declare command text variable
Dim ctInventory

' Declare and set command text variables
Dim ProdID, ProdSold
ProdID = 1
ProdSold = 1

' Declare recordset variable
Dim rsInventory

' Declare variable for number of rows updated 
Dim RowsUpdated

' Declare argument constants
Const adUseClient = 3
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Const adStateOpen = 1

' Create Connection object
Set conSqlServer = CreateObject("ADODB.Connection")

' Define connection string
csSqlServer = "Provider='sqloledb';Data Source='ws04';" & _
  "Integrated Security='SSPI';Initial Catalog='AdventureWorks';"

' Open connection
conSqlServer.Open csSqlServer
        
' Define command text
ctInventory = "SELECT ProductID, Quantity FROM Production.Inventory"

' Create recordset object
Set rsInventory = CreateObject("ADODB.Recordset")

' Set recordset cursor location
rsInventory.CursorLocation = adUseClient

' Open recordset
rsInventory.Open ctInventory, conSqlServer, adOpenStatic, adLockOptimistic, adCmdText 

' Find record, update field, update database
rsInventory.Find "ProductID = " & ProdID
rsInventory.Fields("Quantity") = rsInventory.Fields("Quantity") - ProdSold
rsInventory.Update
  
' Clean up
If conSqlServer.State = adStateOpen then
  conSqlServer.Close
End If

If rsInventory.State = adStateOpen then
  rsInventory.Close
End If

Set conSqlServer = Nothing
Set csSqlServer = Nothing
Set ctInventory = Nothing
Set rsInventory = Nothing
Set ProdID = Nothing
Set ProdSold = Nothing