Re: MS access and postgres "#Deleted" appearing after inserts

drbob wrote:

> I recently experienced the following issue using MS access as an ODBC
> connected frontend to a postgreSQL database:
>
> Upon inserting a new row Access then displays every field in the row as
> "#Deleted". However the insert has not failed, re-querying the table
> displays the newly inserted row.

Enable row versioning in the ODBC driver, then use a before-insert
procedure to query nextval('....') manually from Access and set the
primary key value rather than letting Pg do it during INSERT.

I posted some details about this a while ago; searching the archives for
"MS Access" should turn up some details, sample code, etc. This should
probably go in the FAQ, really.

> The second verification also failed in my case as a different trigger on
> my table validates and changes one of the fields before insert (so the
> value in that field doesn't match the value Access used in the insert
> command). It could also easily fail if it resulted in more than one row
> being returned.

Turning on row versioning will fix that.

> Any comments or suggestions welcome.

Your VB code looks fine (OK, it's VB code, but fine for VB). It's the
same basic approach I used to work around Access's stupidity, and it
seems to work fine. You'll probably find that it works better once you
turn row versioning on, since then Access/ODBC will query only for the
primary key during insert verification, instead of for the whole tuple.

(Why it feels it has to verify inserts in the first place, given that
the database said it worked, I don't understand...).

--
Craig Ringer