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

Modifying Table Data Using ADO Code

   <source lang="vb">

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

</source>
   
  

Update a table

   <source lang="vb">

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

</source>