Skip site navigation (1) Skip section navigation (2)

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

From: Kevin Puetz <puetzk(at)puetzk(dot)org>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution
Date: 2005-01-20 14:53:19
Message-ID: csogl1$8fc$ (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
(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?

In response to

pgsql-odbc by date

Next:From: Ben TrewernDate: 2005-01-20 15:12:26
Subject: Re: Problems with Access XP and text primary keys
Previous:From: Merlin MoncureDate: 2005-01-19 13:30:28
Subject: Re: Driver compatibility

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group