Solved You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

[NigelBishop]

Hi, I'm hoping that one of you will be able to assist. I have migrated the access backend of my database to SQL Server and have linked directly to the tables using dns.
In the front end of the database there is VBA code which creates an audit trail and I believe that it is probably that which is causing the above error message, and is also failing to run.
the code is as follows.

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditBegin
'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database ' Current database
    Dim sSQL As String

    'Remove any cancelled update still in the tmp table.
Set db = DBEngine(0)(0)
sSQL = "DELETE FROM " & sAudTmpTable & ";"
    db.Execute sSQL

    ' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbSeeChanges
End If
    AuditEditBegin = True

Exit_AuditEditBegin:
Set db = Nothing
    Exit Function

Err_AuditEditBegin:
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
Resume Exit_AuditEditBegin
End Function
[code]

My question is how to I correct the above code to function correctly without the error message (if it's possible)

thank you in advance

 

[NauticalGent]

Try:

 db.Execute sSQL, dbOpenDynaset, dbSeeChanges

If your table has an IDENTITY field (PK), dbOpenDynaset is required. I could be wrong but it has worked for me.

 

[NigelBishop]

I tried the above unfortunately I receive the error message below

 

[Minty]

You only need the dbSeeChanges - by definition, an update query has to be a live dataset .

 db.Execute sSQL, dbSeeChanges

edit : Note that you will need it in the Delete execution as well.

 

[NigelBishop]

Thank you, after some searching through the code I eventually got it to work. thank you for your help.