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

> > 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)