'No Current Record' using Access linked to Postgres via ODBC - A possible resolution

Problem
-------------
Using MS Access to read/write a Postgres database using ODBC get the error
message 'No Current Record' when making a simple update to a table record
through the Access default form interface. The table has a integer primary
key and other fields including a timestamp field. The problem is
experienced only on some rows.There is no obvious difference between rows
that work and rows that don't.

Although this problem is similar to that mentioned in several earlier
postings, I found no definitive answer through Google. I have posted the
resolution here in case it helps others with a similar predicament.

Resolution
----------------
When Access issues the update to Postgres though ODBC it creates an update
SQL query that uses all fields in the row as the 'where' clause. This
includes the timestamp field. However, in the roundtrip from Postgres to
Access on row read and then back to Postgres on row update the timestamp
field loses resolution. As a result the 'where' clause does not match the
corresponding Postgres record on the timestamp field. The update fails and
Access reports 'No Current Record'.

The solution is to reduce the resolution of the timestamp stored in
Postgres e.g. using a trigger and the date_trunc() function.

Laurie Burrow

:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.