On 11/22/22 12:53, Brad White wrote:
On 11/18/2022 6:34 PM, Adrian Klaver wrote:
On 11/18/22 16:05, Brad White wrote:
--> The Microsoft Access database engine stopped the process because
you and another user are attempting to change the same data at the
same time.
Code in question:
rst!Update <-- success
rst!QtyDeliverable = rst!Quantity
rst.Update <-- fails here
The wisdom of the internet says that this is most likely with a BIT
field that has null that Access can't handle. But that isn't the case
here. Both are int4 fields and both have values before the update.
The new PostgreSQL timestamp data type defaults to microsecond
precision. This means that timestamp values are stored like 2002-05-22
09:00:00.123456-05. However, Access does not support the extra
precision, so the value that Access uses is 2002-05-22 09:00:00-05.
When one tries to update a record, one gets the error message above
because the value that Access uses in its UPDATE query does not match
the value in the PostgreSQL table, similar to the NULL vs. empty
string conflict that is already reported in this FAQ entry. "
The above is the problem I usually ran into with Access and Postgres
and updating.
Is there a timestamp field in the record you are updating?
UPDATE:
Yes, there are 5 timestamp fields.
It seems unlikely to be the culprit for 3 reasons.
1) It worked fine in v9.4
2) It worked the previous 4 times I saved that record in v12.
3) As the data came from Access, there is no data in any of the fields
in the last three decimal places.
ex. 45.234000
But as it is the best lead I have, and it could still be the culprit
until proven otherwise, I'm working to convert those 5 fields from
timestamp to timestamp(3).
It is worse then that:
https://learn.microsoft.com/en-us/office/troubleshoot/access/store-calculate-compare-datetime-data
Valid time values range from .0 (00:00:00) to .99999 (23:59:59)
So no fractional seconds.
Before you do any of the below I would set up a test table with
timestamps and verify they are the issue.
Of course, PG doesn't allow to edit a table with dependent views.
Which means that I'm attempting to modify a script that will allow me to
save, drop, restore the views.
Of course, PG coerces all table and field names to lowercase unless quoted.
So I have to figure how to recognize all table names and add quotes.
This table is core to the app, so a LOT of the views reference it.
I may not be done anytime soon.
Have a good vacation!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
