You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server t (1 Viewer)

[BennyLinton]

I have a local table "ztblDataChanges" that is working well for the function below, however when i convert this table to a SQL Server table ODBC linked I get the following error... Any ideas?:

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

Function LogChanges(lngID As Long, Optional strField As String = "")
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String

    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !recordid = lngID
        !userName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(Nz(varNew, 0))
        .Update
    End With
    'clean up
    rst.close
    Set rst = Nothing
    dbs.close
    Set dbs = Nothing
End Function

 

[isladogs]

You are editing the data so you do need to add
, dbSeeChanges
at the end of the Set rst line

IDENTITY refers to the pk field

 

[BennyLinton]

I get a syntax error with thei:

    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset, dbSeeChanges

 

[isladogs]

Hi
Sorry missed out a bit of code.
It should have

... OpenRecordset ... , dbOpenDynaset, dbSeeChanges

Apologies I'm writing this on my phone but this should be ok now if you fill in the ... bits

 

[BennyLinton]

If I use the below it is giving a compile error of "expected end of statement" after .OpenRecordset,

Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset, dbOpenDynaset, dbSeeChanges

 

[isladogs]

Your brackets are wrongly placed. Look at this link

http://stackoverflow.com/questions/22486176/how-to-use-the-dbseechanges-option-with-openrecordset-when-accessing-a-sql-serve

 

[BennyLinton]

Success! This worked: Thanks!:

Set rst = dbs.OpenRecordset("ztblDataChanges", dbOpenDynaset, dbSeeChanges)