VBA/Excel/Access/Word/Access/Table Update

Modifying Table Data Using ADO Code

 
Sub IncreaseEstimate()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strSQL As String
    Dim lngUpdated As Long
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open ("Select * from Products")
    strSQL = "UnitPrice < 30000"
    lngUpdated = 0
    rst.Find strSQL
    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("UnitPrice") = rst("UnitPrice") * 1.1
        rst.Update
        rst.Find strSQL, 1, adSearchForward
    Loop
    "Print how many rows are updated
    Debug.Print lngUpdated & " Records Updated"
    rst.Close
    Set rst = Nothing

End Sub

Update a table

 
Sub exaCreateAction()
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "UPDATE BOOKS SET Price = Price*1.1"
    Set qdf = db.CreateQueryDef("PriceInc", strSQL)
    
    qdf.Execute
    
End Sub