Today I started working through using a transaction to cover another complex series of database transactions.
One of my customers was receiving an error message that a record was already deleted during an import.
In this case, the import process takes a while and if it’s being run by multiple people at the same time, could be trying to utilize and process records for deletion that someone else already deleted as they finished the process first.
My idea was to start a transaction before launching the import and then immediately after completing the import commit the transaction checking for errors at that time.
So I started by simply adding about 3 – 4 lines of code:
When I attempted this, it got to the import processing code and began that, but then got an error message about a table being locked. Frankly I wasn’t sure what that was, but I commented out the transaction and it worked fine.
I had an error processing routine so I added code to rollback the transaction if there was an error, however, this caused more errors, so I figured maybe I would try doing the transaction in it’s own workspace.
So my adventure today was trying to repurpose the code to be able to pass in a DAO.Database object from a new workspace and run that via a transaction.
My code is pretty complex and runs through a series of objects and helper functions in various modules using interfaces and polymorphism for each type of file I’m importing. So I had to edit the interface definition and change all of the helper objects. I had spent about an hour by now and needed to move on to work on some other things.
But now my initial code looks more like this:
So once I’m done converting all the internals of ImportEDIdata to use the TxDb object to execute queries and run recordsets, I will be able to test again and I will also have isolated the transaction to just what I’m actually doing.
That means all logging messages and other things the system is doing will happen outside of this transaction. Especially important here is that errors can get logged because they are using a different workspace.
It should be interesting to see what other Workspace transaction limitations I might find.
I know when my transactions were running locally in another program, I had no issues, but on the live system using back end files on the network I was limited to update about 200 records before my transaction failed. It will be interesting to see if this has the same problem and if so how Execute statements vs Recordset iterations affect that issue!
Today I started working through using a transaction to cover another complex series of database transactions.
One of my customers was receiving an error message that a record was already deleted during an import.
In this case, the import process takes a while and if it’s being run by multiple people at the same time, could be trying to utilize and process records for deletion that someone else already deleted as they finished the process first.
My idea was to start a transaction before launching the import and then immediately after completing the import commit the transaction checking for errors at that time.
So I started by simply adding about 3 – 4 lines of code:
Dim ws As Workspace Set ws = DBEngine(0) ws.BeginTrans ' Code to kickoff import process that flows through many routines ws.CommitTrans
When I attempted this, it got to the import processing code and began that, but then got an error message about a table being locked. Frankly I wasn’t sure what that was, but I commented out the transaction and it worked fine.
I had an error processing routine so I added code to rollback the transaction if there was an error, however, this caused more errors, so I figured maybe I would try doing the transaction in it’s own workspace.
So my adventure today was trying to repurpose the code to be able to pass in a DAO.Database object from a new workspace and run that via a transaction.
My code is pretty complex and runs through a series of objects and helper functions in various modules using interfaces and polymorphism for each type of file I’m importing. So I had to edit the interface definition and change all of the helper objects. I had spent about an hour by now and needed to move on to work on some other things.
But now my initial code looks more like this:
Dim ws As DAO.Workspace,TxDb As DAO.Database Set ws = DBEngine.CreateWorkspace( "ImportWorkspace", "admin", "",DAO.dbUseJet) DBEngine.Workspaces.Append ws Set TxDb = ws.OpenDatabase(CurrentProject.FullName) ws.BeginTrans Importer.ImportEDIdata CStr(iFile(0)),TxDb ws.CommitTrans
So once I’m done converting all the internals of ImportEDIdata to use the TxDb object to execute queries and run recordsets, I will be able to test again and I will also have isolated the transaction to just what I’m actually doing.
That means all logging messages and other things the system is doing will happen outside of this transaction. Especially important here is that errors can get logged because they are using a different workspace.
It should be interesting to see what other Workspace transaction limitations I might find.
I know when my transactions were running locally in another program, I had no issues, but on the live system using back end files on the network I was limited to update about 200 records before my transaction failed. It will be interesting to see if this has the same problem and if so how Execute statements vs Recordset iterations affect that issue!