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

(not the original poster)

I found this option a while back and it did fix the problem for UPDATES. I
still occasionally see "No Current Record" or #Deleted on INSERTS if the
table has no data which is unique besides the (server-generated) primary
key. I don't know how the "row versioning" option gets Access to generate
the more manageable while clause, but is there any chance that a similar
trick could be applied to use pg_oid_status to cook up a specifier that
gets it on the newly-inserted row?

Merlin Moncure wrote:

>
> You can also flip the 'row versioning' switch in the drver...this causes
> it to generate a where clause based on the key only. This was discussed
> in depth a couple of months back on this list.
>
> Merlin
>
> -----Original Message-----
> From: pgsql-odbc-owner(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of
> laurie(dot)burrow(at)powerconv(dot)alstom(dot)com Sent: Thursday, January 06, 2005 4:51
> AM To: pgsql-odbc(at)postgresql(dot)org Subject: [ODBC] '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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org