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
> Transaction Left Open When Pasting Multiple Rows Into Subform, Access 2016    
 
   
matter
post Jan 24 2020, 02:31 PM
Post#1



Posts: 4
Joined: 24-January 20



Paste in multiple rows into a subform's datasheet.
In Form_BeforeUpdate, cancel the event. We often do this during validation to prevent the record from being saved.

Now there is a transaction left open. Any further updates or inserts on this form, or any others will not be saved!
Requerying the subform gives an error: Run-time error '3246': Object not supported in transactions.

It's not obvious to the user that this is happening. In the meantime, any updated records will be uncommitted but lock rows in the backend database.
When the form is closed, all updates/inserts will be lost (rolled back)

- We can reproduce the problem with access backend tables, or ODBC (Oracle, Postgres, Sqlite)
- The problem also occurs if there is a backend constraint or trigger that causes the insert to fail.
- DBEngine(0).CommitTrans or Rollback returns Run-time error '3034': You tried to commit or rollback a transaction without first beginning a transaction
- There is no problem cancelling the event when pasting in a single row.

The underlying bug appears to be the same as here: https://stackoverflow.com/questions/1971264...cords-in-access

So besides requerying the subform and catching the error, is there a better way to detect when you're in a non-explicit transaction?
Is there a better way to close the transaction besides re-opening the form?
Attached File(s)
Attached File  transaction_bug_standalone.zip ( 33.18K )Number of downloads: 3
 
Go to the top of the page
 
GroverParkGeorge
post Jan 25 2020, 10:56 AM
Post#2


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


My testing indicates the Before Update event will fire for each attempted insert. I.e. if you try to copy/paste three records, it will fire three times, once for each attempted insert. That makes sense. As long as each one passes validation, the process continues. However, I think this is what happens. When you interrupt the flow using this code in your sample database, remaining records in the queue will indeed remain in transaction because you've never allowed the first one that fails to complete and subsequent inserts can't get processed.They'll sit open and every time you to try to do anything, they're waiting to trap you again.

I guess that this does present a problem. I think the lesson is that you can't use ONE Before Update event to validate MULTIPLE inserts in a transaction.

I'm not sure what the workaround is. It's interesting enough to see what the MS Access team thinks and if I'm right about what's happening.

Stay tuned.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TheSmileyCoder
post Jan 25 2020, 11:28 AM
Post#3


UtterAccess VIP
Posts: 1,528
Joined: 19-January 12
From: Denmark, Copenhagen


Have you tried in the BeforeUpdate event to include:
CODE
Cancel=True
Me.Undo

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
GroverParkGeorge
post Jan 25 2020, 11:34 AM
Post#4


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


It doesn't seem to help to add Me.Undo.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 25 2020, 11:38 AM
Post#5


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


If I'm right, the problem is not the current record that gets cancelled. Undo would impact the first one, but that still leaves any remaining inserts hanging in an incomplete transaction.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
matter
post Jan 27 2020, 10:12 AM
Post#6



Posts: 4
Joined: 24-January 20



Thanks for the idea.
CODE
Cancel=True
Me.Undo
This still causes the problem, however Me.Undo by itself seems to work.
We'll do some more testing to see how that goes.

One other quirk to this problem is when a database trigger or constraint raises an exception.
This also leaves an open transaction.
Trapping the ODBC error in the subform's Form_Error and calling Me.Undo seems to help.
So moving our validations to backend ODBC database is an option.
Go to the top of the page
 
TheSmileyCoder
post Jan 27 2020, 10:26 AM
Post#7


UtterAccess VIP
Posts: 1,528
Joined: 19-January 12
From: Denmark, Copenhagen


In general, when importing data known to be ....of lesser quality, I will import into a temp table, and from there update the production table. This allows me to perform pre-import checks.

You could consider if it makes sense to Paste your data into a temp table, and then run code to insert into your production tables, checking for potential issues before hand.

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
matter
post Jan 27 2020, 11:37 AM
Post#8



Posts: 4
Joined: 24-January 20



For importing data we do use staging tables with validations before moving into a production table.

We have a subform with validations where a user typically adds one row or edits one field at at time.
The validations work well for these cases, providing immediate feedback.

The problem case is copying several child records from one parent record to another.
Not normal, but it happens from time to time.

Seems the Undo isn't quite helping as I thought.
For my tests, I'm triggering the problem, and clearing the pencil icon.
Then update a different record, save, close the form and see if the update was rolled back.
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 11:42 AM
Post#9


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


As I tried to say earlier, I am fairly sure this approach is not going to work because pasting multiple records into the form triggers MULTIPLE Before Update events, one for each potential new record.

Even if you do bail out of one of those attempted inserts along the way, the remaining records in the multiple insert transaction are waiting in an open transaction to trigger the next Before Update event. I may be wrong, of course, about the reasoning there. However, I am quite certain the only valid workaround is going to be pasting into a temp table as an intermediate step and doing individual validations on each record before trying to insert it into the target table.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
matter
post Jan 28 2020, 08:54 AM
Post#10



Posts: 4
Joined: 24-January 20



We do expect Before_Update to fire multiple times during a paste so we can inspect each row. With a few more hacks, I think we can get the Me.Undo to work... at least prevent leaving a transaction open.

Validating data in a staging table is a good practice for importing data, but it's not going to fit for our subforms where live data is typically entered line by line.

In our environment, the open transaction bug is a huge problem. We've had one person enter data for 30 minutes, even print it out, and then everything was wiped out when the form was closed.
While the transaction is open, locks are created on any updated data. When you're dealing with totals like inventory, this means other users can't update data.

I'll update if we manage to get in contact with Microsoft.
Go to the top of the page
 
GroverParkGeorge
post Jan 28 2020, 08:58 AM
Post#11


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


May I offer a recent blog article I wrote as one of the main reason I would probably not invest more of your time here?

Sometimes the challenge of solving an apparently unsolvable problem drives us to new and exciting discoveries and solutions. Sometimes it's just a waste of time.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 02:36 AM