Access Update error: solution.

A comment on the MS access/PG problem.

We experienced this problem as well and I isolated the problem to access's support of timestamp.
PG defaults to timestamp(6),   While access only supports timestamp(2).

When access fills the grid from the table, the values are truncated to ts(2).

As the grid is updated, a sql update statement is prepared in the background.
all fields except for those updated(by the grid) are used in the construction of the where clause.
For most cases this means all of timestamp columns are compared.  Ie
Update remote x
Where local.ts1 = remote.ts1  and .....
The PG timestamp is compared to the local access copy.
Because of the truncation  no row is found in PG, and Access reports that someone else updated the record.

We found the solution to be that all timestamp columns needed to be set to timestamp(0).  Once we did this the issue
wentaway.  

Back to the original problem.
Serial is just a notational convenience. When created the pg engine will rewrite the statement to 1) Generate a
sequence,then 2) create column as int and a default value as nextval(seqName).   The datatype remains recorded as
Serial,which is not a standard SQL datatype.  

You can manually do the same yourself - create the sequence, then assign the column default, or easier - create the
tableas serial, then change the datatype to int.  
PG will automatically create the seq and assign the default.  Changing the datatype will leave the default and sequence
intact.

Once datatype is int, Access will recognize it.

Doug


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer
Sent: Friday, May 21, 2010 3:16 AM
To: Richard Broersma
Cc: tlange@gwdg.de; pgsql-sql@postgresql.org
Subject: Re: [SQL]

On 21/05/2010 9:56 AM, Richard Broersma wrote:
> On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de>  wrote:
>
>> I'm new to triggers in PostgreSQL. I have to create a trigger on insert to
>> increment a sequence to overcome MS-Access' limitation in acknowledging
>> serial "datatype".
>
> Uh?  Access doesn't need to acknowledge the serial datatype.
> At-least in recent versions auto increment is recognized by MS-Access
> just fine (I'm guessing this is due to the Return clause which the
> ODBC driver automatically calls).

Really?

I had problems with Access complaining that the object it just inserted
had vanished, because the primary key Access had in memory (null) didn't
match what was stored (the generated PK). I had to fetch the next value
in the PK sequence manually and store it in Access's record before
inserting it to work around this.

I wouldn't be surprised if this actually turned out to just require some
bizarre ODBC driver parameter change, but I never figured it out and I
couldn't find any info about it on the 'net.

For the original poster: I posted some information about this at the
time I was working on it, so search the archives of this list for MS Access.

I eventually ditched Access entirely as the user who was demanding the
use of MS Access relented (phew!), so I put together a simple web-app to
do what they wanted in a day. Hopefully I'll never need to go near ODBC
again, because it's a truly "special" way to talk to PostgreSQL.

--
Craig Ringer


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql