MS access and postgres "#Deleted" appearing after inserts

[drbob <drbob(at)gmx(dot)co(dot)uk>]

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: 
<http://support.microsoft.com/kb/128809>

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  
<http://archives.postgresql.org/message-id/6C0CF58A187DA5479245E0830AF84F420802A0(at)poweredge(dot)attiksystem(dot)ch 
> 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 
<http://www.techonthenet.com/access/queries/passthrough2.php> (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