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:
<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@poweredge.attiksystem.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