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

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

From: laurie(dot)burrow(at)powerconv(dot)alstom(dot)com
To: pgsql-odbc(at)postgresql(dot)org
Subject: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution
Date: 2005-01-06 09:51:03
Message-ID: OF8CD3226E.3C99D6E9-ON80256F80.006203D1-80256F81.003634F8@transport.alstom.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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.

pgsql-odbc by date

Next:From: Merlin MoncureDate: 2005-01-06 13:19:24
Subject: Re: 'No Current Record' using Access linked to Postgres via ODBC - A possible resolution
Previous:From: Pailloncy Jean-GerardDate: 2005-01-04 23:55:09
Subject: iodbc & OpenBSD

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