Dim db As DAO.DBEngine
Const maxDefault& = 9500
Set db = DBEngine
' temporary increase the lock files
db.SetOption dbMaxLocksPerFile, 20000
db.BeginTrans
With rs
If Not (.BOF And .EOF) Then
.MoveFirst
End If
Do Until .EOF
.Edit
'update the field
!FieldName = "value"
.Update
.MoveNext
Loop
End With
db.CommitTrans
db.SetOption dbMaxLocksPerFile, maxDefaultTransaction is a new world for me. But it's never too late to learn something. I'll try it Monday morning as soon as I'm back to my desk to see if it can be used.
In dao you loop the records then make one change then update the table.
' Modify records in memory
Do Until rs.EOF
rs.Fields("Status").Value = "Processed"
rs.MoveNext
Loop
' Commit all changes at once
rs.UpdateBatch| Feature | DAO Transaction | ADO Batch Update |
|---|---|---|
| Purpose | Ensures atomicity across multiple operations (insert/update/delete) | Allows editing multiple records in memory before committing |
| Scope | Applies to any DAO operation (SQL, recordset edits) | Applies only to disconnected ADO recordsets |
| Control | Manual: BeginTrans, CommitTrans, Rollback | Automatic: changes cached until UpdateBatch |
| Error Handling | You control rollback on failure | ADO handles conflicts via Filter, Status, or CancelBatch |
| Performance | Fast for bulk SQL operations | Efficient for client-side editing with fewer round-trips |
| Concurrency | Depends on locking mode (dbOptimistic, dbPessimistic) | Uses adLockBatchOptimistic for disconnected editing |
| Use Case | Updating multiple tables or complex logic | Updating many rows in one table with minimal DB hits |
SummaryI have a continous form that is used for saving new records. Ater a few input, or 20 or more, if a specific condition is met, I have to update a field named "Identifier" of all data which have been saved by this user.
Wouldn’t that be the userid, perhaps with reference to the condition?
I'm sorry. I don't understand what you're trying to tell me. The same userID may have thousands of records in the table (previous days, previous hours).
The recordset is :
The only way to be prepared, is to check all the order sheets one by one to see if the contents are met with a business rule.
Public Function GetPK_Criteria(PK_FieldName As String, Frm As Access.Form)
Dim fltr As String
Dim I As Long
Dim val As Long
Dim rs As DAO.Recordset
'Provide a column number if not using the bound column
Set rs = Frm.RecordsetClone
If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
val = rs.Fields(PK_FieldName)
If fltr = "" Then
fltr = val
Else
fltr = fltr & ", " & val
End If
rs.MoveNext
Loop
If fltr <> "" Then
fltr = " IN (" & fltr & ")"
End If
GetPK_Criteria = fltr
End FunctionIN (1, 2, 3, 4, 17, 18, 39, 40, 60)dim Criteria as string
criteria = GetPK_Criteria("ProductID", Me)
StrSql
dim Criteria as string
criteria = GetPK_Criteria("ProductID", Me)
StrSql = "Update table1 set somefield = "X" where SomePK " & Criteria
= "Update table1 set somefield = "X" where SomePK " & Criteria
Currentdb.execute strSqldim Criteria as string
criteria = GetPK_Criteria("ProductID", Me)
Criteria = Criteria & " AND Status = 'Current'"Here is the double safe construct with both a transaction and with echo off. My guess is just one of these is good enough.
Sub UpdateRecords()
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo Cleanup
Application.Echo False
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)
db.BeginTrans
Do While Not rs.EOF
rs.Edit
rs!SomeField = "NewValue"
rs.Update
rs.MoveNext
Loop
db.CommitTrans
Cleanup:
If Application.Echo = False Then Application.Echo True
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set db = Nothing
End SubUnfortunately it's not a filtered form, but a form with Data Entry set to Yes.
with recordsetclone
.movefirst
currentdb.execute "UPDATE myTable SET identifier="something" WHERE UserFK=" & !userFK & " and recordPK>=" & !recordPK
end withIt is not reading the PKs of the table it is reading the PKs of the records in the forms recordset. This can be a filtered or queried set of records.
