MS access and postgres "#Deleted" appearing after inserts

Hello,

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.

This was due to the fact that after every insert operation access
performs a query to verify the insert. It attempts the verification
twice, once using a SELECT based on the primary key, if that fails it
performs a SELECT using every other field it inserted in the row. See:


My issue arose because the primary key in the table is a sequence and
access doesn't by default know the next value (it's generated by a
trigger upon insert) so the first verification attempt failed.

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.

There has been some discussion of this issue on the list before e.g
> but I wasn't able to find any sample work-around code on here so I
thought I'd post this for anyone else with the same problem.

I fixed the problem by using a VBA function that fetches the next
sequence value from postgres with a passthrough query so Access can then
set the primary key directly and knows what it is for the subsequent
verification, rather than relying on the upon insert trigger. I followed
the example on this website
(it's for
oracle but very easily modified for postgres) . The adapted VBA function
is below, you need to provide a valid connect string (I just copied the
connect string from the properties dialog of a passthough query created
in the access GUI).

Function AssignNextVal(sequence As String) As Long

Dim db As Database
Dim LPassThrough As QueryDef
Dim Lrs As DAO.Recordset
Dim LSQL As String

On Error GoTo Err_Execute

Set db = CurrentDb()

'Create a temporary passthrough query to retrieve the NextVal
from an Oracle sequence
Set LPassThrough = db.CreateQueryDef("qryTemp")

'Use PostgreSQL ODBC connection
LPassThrough.Connect = "Connect String here"
LPassThrough.SQL = "SELECT nextval('" + sequence +
"'::regclass)::integer AS NV;"
LPassThrough.ReturnsRecords = True

Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)

'Retrieve NextVal from Oracle sequence
If Lrs.EOF = False Then
AssignNextVal = Lrs("NV")
Else
AssignNextVal = 0
End If

'Remove query definition when done
CurrentDb.QueryDefs.Delete "qryTemp"

Exit Function

Err_Execute:

'Remove query definition when done
CurrentDb.QueryDefs.Delete "qryTemp"

'Return 0 if an error occurred
AssignNextVal = 0

End Function

Then in I add something similar to the below as a before Insert event to
forms which interact with linked tables (where ID is the primary key):

Private Sub Form_BeforeInsert(Cancel As Integer)

ID = AssignNextVal(sequence_name)

End Sub

Any comments or suggestions welcome.

regards,

drbob





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

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

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