From: Joel Burton <jburton@scw.org>
To: pgsql-general@postgresql.org
To: pgsql-interfaces@postgresql.org
Subject: (Fwd) RE: PostgreSQL, ODBC, Access (solution for me)
Date: Tue, 25 Jul 2000 17:03:52 -0400
Message-ID: <397DC878.10908.293E8700@localhost> (raw)
> > 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@scw.org
Support Center of Washington (www.scw.org)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jburton@scw.org, pgsql-general@postgresql.org
Subject: Re: (Fwd) RE: PostgreSQL, ODBC, Access (solution for me)
In-Reply-To: <397DC878.10908.293E8700@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox