Code works without Transaction, but not with

[Jon Ley]

I am running some code to update many tables in my database, and I want an
'all or nothing' result. I am therefore performing all the updates within a
transaction. For some reason, I am encountering a situation where the code
(which usually works fine) is not working with one particular set of data. In
an attempt to debug the code I commented out the BeginTrans and CommitTrans
statements. All the data updated fine. With the transaction statements back
in place, the code runs through without any errors, however some of the
updates do not work (but some do).

I have tried all sorts of things - compact and repair both front end and
back end databases; decompile both databases. The decompile seemed to corrupt
the back end, so I created a new back end database, imported the table
definitions from the old back end (uncorrupted copy), then linked the old
tables into the new back end and transferred the data across. This cured my
corruption problem, but the transaction still does not work.

Anyone out there got any ideas? (Access XP, by the way)

 

[Allen Browne]

DAO transaction or ADO?

There are several things that can go wrong in the process. Any chance you
have dangling transactions in place? This typically happens if your error
handler does not rollback. Since DAO supports 5 nested levels of
transaction, when you run the code again, you still have a previous level of
uncommitted transaction, and so the data is not committed even thouth the
2nd pass does commit, because there is a higher level uncommitted
transaction still pending.

You might also try unchecking the box under:
Tools | Options | Advanced | Open db using record-level locking.
I experienced a case where the transaction failed if that box was checked.
(The case involved A97 back end data, which doesn't actually support
record-level locking.)

 

[Jon Ley]

Allen,

THANK YOU!! (but ...)

DAO transaction or ADO? DAO

Any chance you have dangling transactions in place? No.

You might also try ...

That fixed it! So you can now report that this also happens with A2K and
A-XP back ends (my original was 2K, the new one I created when I had a
corruption problem was XP. The fix works on both of them).

And now for the 'but ...'

So how do I guarantee that all my users have this box unchecked? This is a
serious issue for me as the code fails silently, and the user has no
indication that it hasn't worked unless they happen to manually check
afterwards that the relevant records have been updated. This has major
implications for our business as a whole, as we have numerous Access
applications that use transactions, and I am now very worried that the data
integrity of some or all of these apps may have been compromised without our
knowledge. Has MS issued a fix for this?

Regards,

Jon.

 

[Jon Ley]

It's OK, I've answered my own question. I can use GetOption/SetOption to
check and correct the setting. I am still very concerned about the
possibility of previously corrupting my data though.

Jon.

 

[Allen Browne]

Very interesting! This appears to be a problem with JET then, not merely an
issue relating to converting between different versions of JET.

I did report this issue to MS, but I was not able to get permission from the
client to give them the large and sensitive database, so AFAIK, they did not
actually see the problem.

And for the "But...":
In your startup code, you could include:
Application.SetOption("Use Row Level Locking", 0)

 

[Jon Ley]

Allen,

Thanks, that's pretty well what I did, except that I needed to close and
re-open the database for the change to take effect. I ended up with:

If GetOption("Use Row Level Locking") Then
SetOption "Use Row Level Locking", False
MsgBox "Your database options are set incorrectly and will now " & _
"automatically be adjusted. You will need to restart this " & _
"application for the corrected settings to take effect.", _
vbInformation, "System message ..."
Application.Quit
End If

If you're interested, I have a trimmmed down version of my app and data that
will demonstrate the problem. I would be quite happy to let you have this if
you think that MS will take any notice.

Best regards,

Jon.

 

[Allen Browne]

Thanks, Jon, but I can't get them to fix even basic stuff like getting
Access to sort correctly, so I doubt there's any point in sending this to
them.

This is the sorting problem:
http://allenbrowne.com/bug-08.html

If find it really embarrassing that Access cannot reliably handle a query
that is this simple:
SELECT MyField FROM MyTable ORDER BY MyField DESC;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks, that's pretty well what I did, except that I needed to close and
re-open the database for the change to take effect. I ended up with:

If GetOption("Use Row Level Locking") Then
SetOption "Use Row Level Locking", False
MsgBox "Your database options are set incorrectly and will now " &
_
"automatically be adjusted. You will need to restart this "
& _
"application for the corrected settings to take effect.", _
vbInformation, "System message ..."
Application.Quit
End If

If you're interested, I have a trimmmed down version of my app and data
that
will demonstrate the problem. I would be quite happy to let you have this
if
you think that MS will take any notice.

Best regards,

Jon.

 

[Jon Ley]

Fair comment!

Of course, this gives me another potential problem that has already occurred
once in the short time since issuing the fix to the Transaction problem.
Given that Access is now locking pages of records at a time, rather than
individual records, I am more likely to get record locking conflicts. I never
did get around to learning how to write my code to handle this - the
environment I work in rarely has more than one user editing a given record at
any one time, so I haven't needed to.

Jon.

Thanks, Jon, but I can't get them to fix even basic stuff like getting
Access to sort correctly, so I doubt there's any point in sending this to
them.

This is the sorting problem:
http://allenbrowne.com/bug-08.html

If find it really embarrassing that Access cannot reliably handle a query
that is this simple:
SELECT MyField FROM MyTable ORDER BY MyField DESC;

 

[Allen Browne]

That's very unlikely to be an issue for you.

What I do is to use optimistic locking, and include a page in the
instruction manual on how to handle the write conflict dialog. We train the
users on that, but most of them tell us later that they never see it. Access
is pretty good as a multi-user database.