VBA/Excel/Access/Word/Access/Recordset Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 15:46, 26 мая 2010
Modifying One Record at a Time
<source lang="vb">
Sub IncreaseEstimateImproved()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset Dim lngUpdated As Long rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenDynamic rst.LockType = adLockOptimistic rst.Open ("Select * from Products " & _ "WHERE UnitPrice < 30000") Do Until rst.EOF lngUpdated = lngUpdated + 1 rst("UnitPrice") = rst("UnitPrice") * 1.1 rst.Update rst.MoveNext Loop Debug.Print lngUpdated & " Records Updated" rst.Close Set rst = Nothing
End Sub
</source>
Performing Batch Updates
<source lang="vb">
Sub BatchUpdates()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset Dim strSQL As String Dim lngUpdated As Long rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenKeyset rst.CursorLocation = adUseClient rst.LockType = adLockBatchOptimistic 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.Find strSQL, 1, adSearchForward Loop rst.UpdateBatch Debug.Print lngUpdated & " Records Updated" rst.Close Set rst = Nothing
End Sub
</source>
Update Recordset
<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>