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

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@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of
laurie.burrow@powerconv.alstom.com
Sent: Thursday, January 06, 2005 4:51 AM
To: pgsql-odbc@postgresql.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
simpleupdate to a table record through the Access default form interface. The table has a integer primary key and other
fieldsincluding a timestamp field. The problem is experienced only on some rows.There is no obvious difference between
rowsthat 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
thenback to Postgres on row update the timestamp field loses resolution. As a result the 'where' clause does not match
thecorresponding 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
purposeor store or copy the information in any medium.  

(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@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of
> laurie.burrow@powerconv.alstom.com Sent: Thursday, January 06, 2005 4:51
> AM To: pgsql-odbc@postgresql.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