Thread: 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
drbob wrote: > 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. Enable row versioning in the ODBC driver, then use a before-insert procedure to query nextval('....') manually from Access and set the primary key value rather than letting Pg do it during INSERT. I posted some details about this a while ago; searching the archives for "MS Access" should turn up some details, sample code, etc. This should probably go in the FAQ, really. > 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. Turning on row versioning will fix that. > Any comments or suggestions welcome. Your VB code looks fine (OK, it's VB code, but fine for VB). It's the same basic approach I used to work around Access's stupidity, and it seems to work fine. You'll probably find that it works better once you turn row versioning on, since then Access/ODBC will query only for the primary key during insert verification, instead of for the whole tuple. (Why it feels it has to verify inserts in the first place, given that the database said it worked, I don't understand...). -- Craig Ringer