Re: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution - Mailing list pgsql-odbc
From | Kevin Puetz |
---|---|
Subject | Re: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution |
Date | |
Msg-id | csogl1$8fc$1@sea.gmane.org Whole thread Raw |
In response to | Re: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution ("Merlin Moncure" <merlin.moncure@rcsonline.com>) |
List | 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@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
pgsql-odbc by date: