Re: MS access and postgres "#Deleted" appearing afterinserts
am 13.03.2009 23:32:30 von Adrian Klaver----- "drbob"
> 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
>
> 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
>
You don't by chance have a timestamp field in the Postgres table? I have seen this when the timestamp field has millisecond precision. Access has problems with that. To solve I set timestamps to timestamp(0).
Adrian Klaver
aklaver@comcast.net
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc