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

(Fwd) RE: PostgreSQL, ODBC, Access (solution for me)

From: "Joel Burton" <jburton(at)scw(dot)org>
To: pgsql-general(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org
Subject: (Fwd) RE: PostgreSQL, ODBC, Access (solution for me)
Date: 2000-07-25 21:03:52
Message-ID: 397DC878.10908.293E8700@localhost (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfaces
> > Here's a bothersome issue:  I've got the most recent versions of
> > Postgres, ODBC client for Win32, and Access 97.  My client can 
enter
> > new records fine via a linked table.  However, when she goes 
back to
> > add data to a column, she gets the following error:
> >
> > message box title: "Write Conflict"
> > description: "This record has been changed by another user 
since you
> > started editing it.  If you save the record, you will overwrite
> > the changes the other user made." buttons: "Copy to Clipboard" and
> > 
"Drop
> > Changes".

It appears that *once* Access finds something unique about a 
record, it uses that to differentiate records. (Check out the SQL log
to see) However, a new field in Access has no key *until* 
PostgreSQL
gets it (if you're using a SERIAL field type), and the default values
for other fields don't appear either. So, the trick is to have Access
deposit a unique value (in this case, a timestamp) into each field.

What works for me (even in datasheet view):

1. Create a table w/a timestamp field.

CREATE TABLE Foo (id SERIAL PRIMARY KEY, fullname VARCHAR(30) 
NOT NULL, dt TIMESTAMP DEFAULT 'now' NOT NULL);

Then, in Access:

Don't use *table* datasheet view. Create a form w/the fields you 
want, and use that *form*datasheet view. Set it up so that Access 
has a DefaultValue property of Now() for the "ts" column. (In 
addition, while you're there, you might want to lock/hide the ts 
column, and lock the serial column, as Access will let you renumber 
a PostgreSQL serial field, which I think is a Bad Thing [YMMV].)

Then use the datasheet view. Since the "dt" column should be 
different for each record *from the moment of inception*, this gives
Access something really unique to hang its hat on.

Works for me; let me know if it doesn't work for you.

--

Has anyone ever collected a FAQ of Access-on-Postgresql? I've got 
a
few tips (nothing heavy, just the usual use-float-instead-of-
decimal-for-currency), and suspect others have a few.

--
Joel Burton, Director of Information Systems -*- jburton(at)scw(dot)org
Support Center of Washington (www.scw.org)

pgsql-interfaces by date

Next:From: Nagy Laszlo Zsolt , KLTE TTK pm1Date: 2000-07-26 12:21:44
Subject: Re: PyGreSQL and transactions
Previous:From: David C MudieDate: 2000-07-25 20:10:38
Subject: Re: JDBC DateTime broken in postgresql-7.02?

pgsql-general by date

Next:From: Ian TurnerDate: 2000-07-25 21:18:18
Subject: Re: function language type?
Previous:From: Jan WieckDate: 2000-07-25 20:54:04
Subject: Re: General Trigger Functions

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