Users report that when editing data via Access they cannot make changes to records with high ID numbers

[jajwilson]

Angela Trentascoste, working on the OxRep database, is complaining that when she tried to edit data in database tables using Access (via ODBC) she's having problems editing records with ID numbers greater than the 2000s. I have reproduced this issue myself using the same database. Essentially, when she tries to update any of the data in a record with a high ID number, she gets the following error:

The exact maximum id up to which she can edit records seems to vary, but so far it's always at some point in the 2000s.

I would put this as a High priority, but it's affecting her work at present, so making it critical.

[jajwilson]

Ah - I've since heard that in some tables the same error occurs when trying to edit any records, not just ones with high ID numbers - so the bug is a bit inconsistent.

[thestoat]

The screenshot is showing a password authentication error with SSL off. SSL needs to be on.

[jajwilson]

D'Oh! That's because I attached the wrong screenshot! Here's the correct screenshot for this issue...

[thestoat]

Are you able to confirm that nobody else is using the database at the same time? The message suggests somebody else is?

[jajwilson]

Yes. And I've tested this before with Pamela - the message should only appear when you have two people trying to add exactly the same record at exactly the same time. We didn't come across this bug when we were checking this before, but then we were working with a much smaller table in a much smaller database.

[jajwilson]

"add" = "edit" in the note above - sorry!

[thestoat]

What you seem to be saying is that a tool we have no control over seems to be having problems when writing to another tool we have no control over. This is going to be problematic to track down. In the first instance I am minded to disable all ODBC access from everyone except Angela to see if this fixes the problem. What do you think?

[jajwilson]

I think that’s a very bad idea if it applies to all database, a not-so-bad idea if it only applies to that particular database. We’ll need to inform Nick Ray and Angela what we’re up to assuming it’s the latter.

It may well be that this is a problem with Access, albeit a rather mystifying one – as editing records with lower ID numbers continues to work even after editing higher ones fails – so it’s not simply that the OBBC connection is being disconnected. We do need to check what’s going on in ORDS though, especially given that the new ODBC stuff should ideally be up and working soon to enable Scott and Kris to open-source a reasonably stable version of the code.

[thestoat]

No, as discussed, any changes I make will only be to that specific database so that we can understand what is going on.

[thestoat]

Please append a link to the database so that I can understand which to work on.

[jajwilson]

https://app.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=227552&dbProjectId=223159&pName=Oxford%20Roman%20Economy%20Project

[jajwilson]

I’ve found this page describing the problem: https://social.msdn.microsoft.com/Forums/office/en-US/4bea340e-86a2-4940-bac4-a98f32a70cc8/odbc-linked-sql-server-table-reports-this-record-has-been-changed-by-another-user-on-edits?forum=accessdev

Unfortunately in our case navigating to the end of the table does not unlock the ability to make edits, nor are there any bit fields that might be misbehaving.

Some suggestions indicate that having a datestamp field in the underlying database might help, but this sounds rather unlikely.

[jajwilson]

It does seem that the point at which records with higher ID numbers cannot be edited is more consistent than I initially thought. With the public_sites table the trouble begins at record 2860. I can't spot anything obviously odd about it, but the timestamp field does jump ahead a bit from the previous record.

[thestoat]

As discussed, I plan to reset the password for the odbc user accessing the data and create a second odbc user too. Then we can monitor if the problem continues.

[jajwilson]

Disconencting the ORDS from the underlying postgres server still failed to fix this issue - so it looks as though it's an Access bug. We need to report this to Microsoft, but otherwise it's closed (and unresolved)