UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> When Should We Establish A New Workspace For Transaction Processing?, Access 2016    
 
   
allen_edgar
post Aug 29 2019, 10:34 AM
Post#1



Posts: 23
Joined: 7-May 17



When I read the Microsoft documentation for Access transaction processing, the articles usually show examples using a new DAO workspace with the transaction processing encapsulated in the workspace.

Dim wrk As Workspace
Dim dbs1 As DAO.Database, dbs2 As DAO.Database

Set wrk = CreateWorkspace("myWorkSpace", "admin", "", dbUseJet)
Workspaces.Append wrk
Set dbs1 = wrk.OpenDatabase(CurrentDb.Name)
Set dbs2 = wrk.OpenDatabase(CurrentDb.Name)

On Error GoTo trans_Err
wrk.BeginTrans
dbs1.Execute "INSERT INTO WorkOrderJournal (WODefId, ScheduleDate, WOIssue) VALUES (" + Str(defId) + ", " + strDate + "," + Str(issueNum) + ")"
dbs2.Execute "INSERT INTO WorkOrderJournalEquipment (EquipmentId, WOJournalId) SELECT WODefToEquipment.EquipmentId," + _
Str(journalId) + " FROM WorkOrderDefs INNER JOIN WODefToEquipment ON WorkOrderDefs.WOdefId = WODefToEquipment.WODefId " + _
"WHERE WorkOrderDefs.WOdefId =" + Str(defId)
wrk.CommitTrans dbForceOSFlush

But I have also see examples that simply perform the transaction in workspace(0) using DBEngine.BeginTrans method. I believe this is equivalent to DBEngine.Workspaces(0).BeginTrans. In either case, the transaction processing takes place in the default workspace:

Set dbs = CurrentDb
On Error GoTo transError
DAO.DBEngine.BeginTrans
dbs.Execute "INSERT INTO WorkOrderJournal (WODefId, ScheduleDate, WOIssue) VALUES (" + Str(defId) + ", " + strDate + "," + Str(issueNum) + ")"
dbs.Execute "INSERT INTO WorkOrderJournalEquipment (EquipmentId, WOJournalId) SELECT WODefToEquipment.EquipmentId," + _
Str(journalId) + " FROM WorkOrderDefs INNER JOIN WODefToEquipment ON WorkOrderDefs.WOdefId = WODefToEquipment.WODefId " + _
"WHERE WorkOrderDefs.WOdefId =" + Str(defId)
DAO.DBEngine.CommitTrans

I have used both methods successfully.
Note that in both examples, the tables involved are already in the currentdb so a new connection to a database wasn't necessary.
My question: When is DAO.DBEngine.BeginTrans appropriate and when is the creation of a new workspace for the transaction really necessary other than when the transaction involves connection to an external table that is not already linked to my front-end.
Go to the top of the page
 
PhilS
post Aug 30 2019, 09:08 AM
Post#2



Posts: 616
Joined: 26-May 15
From: The middle of Germany


QUOTE
I believe this is equivalent to DBEngine.Workspaces(0).BeginTrans. In either case, the transaction processing takes place in the default workspace:

Yes, this is correct.

QUOTE
My question: When is DAO.DBEngine.BeginTrans appropriate and when is the creation of a new workspace for the transaction really necessary other than when the transaction involves connection to an external table that is not already linked to my front-end.

Good question!

There can be only one active transaction per workspace at the same time. So, the only situation when you really must use a different workspace for a transaction is, when you need two (or more) independent transactions at the same time.

One Example:
You process several records in a multi-step operation in VBA within a transaction. This may fail or succeed as a whole. If it fails, all write operations are rolled back.
At the same time you want to write information about all the steps of the above processing to a log-table. These records should always persist, even when the above operations fails and is rolled back.
- You need two different workspaces to achieve this.
This post has been edited by PhilS: Aug 30 2019, 09:09 AM

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 10:10 AM