Re: MS access and postgres "#Deleted" appearing after inserts
От | Adrian Klaver |
---|---|
Тема | Re: MS access and postgres "#Deleted" appearing after inserts |
Дата | |
Msg-id | 1392885749.8264131236983550725.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net обсуждение исходный текст |
Ответы |
Re: MS access and postgres "#Deleted" appearing after inserts
("Greg Cocks" <gcocks@stoller.com>)
|
Список | pgsql-odbc |
----- "drbob" <drbob@gmx.co.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 millisecondprecision. Access has problems with that. To solve I set timestamps to timestamp(0). Adrian Klaver aklaver@comcast.net
В списке pgsql-odbc по дате отправления:
Следующее
От: "Greg Cocks"Дата:
Сообщение: Re: MS access and postgres "#Deleted" appearing after inserts