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

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: drbob <drbob(at)gmx(dot)co(dot)uk>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: MS access and postgres "#Deleted" appearing after inserts
Date: 2009-03-13 22:32:30
Message-ID: 1392885749.8264131236983550725.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc


----- "drbob" <drbob(at)gmx(dot)co(dot)uk> wrote:

> 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
>

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(at)comcast(dot)net

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Greg Cocks 2009-03-13 22:47:43 Re: MS access and postgres "#Deleted" appearing after inserts
Previous Message drbob 2009-03-13 22:13:49 MS access and postgres "#Deleted" appearing after inserts