Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-odbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group