Transcript Record Locking

A few slides on MS Access Record
Locking and Transactions
ODBC and Access Record Locking
When you edit data in a linked SQL database
table using ODBC, Microsoft Access doesn't
lock records; instead, the rules of that SQL
database govern locking. In this instance,
regardless of the record-locking setting you
choose for your database, Microsoft Access
always acts as though the No Locks setting has
been selected.
Where to specify locking in Access
• Advanced tab of tools\options to Set default
• RecordLocks property
– Forms. Specifies how records in the underlying table or
query are locked when data in a multiuser database is
updated.
– Reports. Specifies whether records in the underlying
table or query are locked while a report is previewed or
printed.
– Queries. Specifies whether records in a query (typically
an action query in a multiuser database) are locked
while the query is run.
• DAO. LockEdits property of recordset
Transaction Methods (Access)
• BeginTrans begins a new transaction.
• CommitTrans ends the current transaction and
saves the changes.
• Rollback ends the current transaction and restores
the databases in the Workspace object to the state
they were in when the current transaction began.
Transaction Methods (Access)
Sub PostAccounts()
‘Dim and assign variables including tempws, tempdb, temprs, posting
Posting = False
On Error GoTo ErrorCondition
tempws.BeginTrans
' Start of transaction.
Posting = True
‘complex set of operations here
Posting = False
tempws.CommitTrans
Alldone
‘release variables
ErrorCondition:
MsgBox "Error!"
If Posting Then
tempws.Rollback
End If
Resume Alldone
End Sub
Access Record Selector Icons