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

[lacampeona]

I managed to connect sql server and ms access database and now I am gettting some errror.

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column". This error appears when you open recordset that contains IDENTITY column.

I search on the internet and I find that I have to change and put dbSeeChanges but I dont know the correct syntax

I am sure that this is the problematic line of code:

Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbAppendOnly)

Can someone take a look and try to help me or give me advice?
thank you

Here is my module I use:

Public Function LogDocOpen(obj As Object) As Long
On Error GoTo Err_Handler
'Purpose: Create a log entry for the form/report being opened.
'Argument: The form or report whose opening we are logging.
'Return: Primary key value of the log entry. Zero on error.
'Usage: For a form, set the On Open property to: =LogDocOpen([Form])
' For a report, set the On Open property to: =LogDocOpen([Report])
Dim rs As DAO.Recordset
Dim lngObjType As Long 'acForm or acReport
Dim strDoc As String 'Name of the form/report
Dim lngHWnd As String 'hWnd of the form/report

If mbLogDox Then
strDoc = obj.Name
lngHWnd = obj.hwnd


Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbAppendOnly)

rs.AddNew
rs!OpenDateTime = Now()
rs!CloseDateTime = Null
rs!DocTypeID = DocType(obj)
rs!DocName = strDoc
rs!DocHWnd = lngHWnd
rs!ComputerName = ComputerName()
rs!WinUser = NetworkUserName()
rs!JetUser = CurrentUser()
rs!CurView = CurView(obj)
rs.Update
rs.Bookmark = rs.LastModified
LogDocOpen = rs!LogDocID
rs.Close
End If

Exit_Handler:
Set rs = Nothing
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod & ".LogDocOpen", "Document " & strDoc, False)
Resume Exit_Handler
End Function

 

[theDBguy]

You could try.

Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset + dbSeeChanges, dbAppendOnly)

 

[lacampeona]

Hello
no, is not working
it says invalid argument.

 

[theDBguy]

Hello
no, is not working
it says invalid argument.

Try it on the other end then.

Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbAppendOnly + dbSeeChanges)

 

[lacampeona]

For now is not working.
I think another part of code is also connected with all that
i have also code on close event

i think in this part of code this is also he problematic line:

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

i think i also have to put dbSeeChanges?
can you help me with the correct syntax also here?

thank you

Public Function LogDocClose(obj As Object) As Long
On Error GoTo Err_Handler
'Purpose: Update the log entry created when the form/report was opened, to mark it closed.
' Creates a new entry if the existing one cannot be found.
'Argument: The form or report whose closing we are logging.
'Return: Primary key value of the log entry updated/created. Zero on error.
'Usage: For a form, set the On Close property to: =LogDocClose([Form])
' For a report, set the On Close property to: =LogDocClose([Report])
Dim rs As DAO.Recordset
Dim strSQL As String 'SQL statement
Dim strDoc As String 'Name of the form/report
Dim strWinUser As String 'Name of the Windows user
Dim strJetUser As String 'Name of the JET engine user
Dim strComputer As String 'Name of this workstation
Dim lngObjType As Long 'acForm or acReport
Dim lngHWnd As String 'hWnd of the form/report

If mbLogDox Then
strDoc = obj.Name
strWinUser = NetworkUserName()
strComputer = ComputerName()
lngHWnd = obj.hwnd
lngObjType = DocType(obj)

'Get the log entry when this user on this computer opened this form/report (same name, type and hWnd)
strSQL = "SELECT tblLogDoc.* FROM tblLogDoc WHERE ((tblLogDoc.DocTypeID = " & lngObjType & ") AND (tblLogDoc.DocName = """ & strDoc & _
""") AND (tblLogDoc.DocHWnd = " & lngHWnd & ") AND (tblLogDoc.ComputerName = """ & strComputer & """) AND (tblLogDoc.WinUser = """ & strWinUser & _
""") AND (tblLogDoc.CloseDateTime Is Null) AND (tblLogDoc.OpenDateTime <= Now())) ORDER BY tblLogDoc.OpenDateTime, tblLogDoc.LogDocID;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
If rs.RecordCount > 0& Then
'Log entry found: update as closed.
rs.Edit
rs!CloseDateTime = Now()
rs.Update
Else
'Can't find when document was opened: create a new one.
rs.AddNew
rs!OpenDateTime = Null
rs!CloseDateTime = Now()
rs!DocTypeID = lngObjType
rs!DocName = strDoc
rs!DocHWnd = lngHWnd
rs!ComputerName = strComputer
rs!WinUser = strWinUser
rs!JetUser = CurrentUser()
rs!CurView = CurView(obj)
rs.Update
End If
rs.Bookmark = rs.LastModified
LogDocClose = rs!LogDocID
rs.Close
End If

Exit_Handler:
Set rs = Nothing
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod & ".LogDocClose", "Document " & strDoc, False)
Resume Exit_Handler
End Function

 

[theDBguy]

Maybe you could try.

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, , dbSeeChanges)

 

[lacampeona]

hmmmm no
not working.....
what else do you think could be a problem?

 

[Gasman]

Have you tried googling? as it appears you just need the opendynaset with dbseechanges?
To get an empty rs just use where 1=0 with a select statement.

 

[theDBguy]

hmmmm no
not working.....
what else do you think could be a problem?

When you say "not working," was it giving you any error messages? If you are getting your custom error message, I'd recommend disabling your error handler for the moment, so you can troubleshoot the problem better.

 

[lacampeona]

When you say "not working," was it giving you any error messages? If you are getting your custom error message, I'd recommend disabling your error handler for the moment, so you can troubleshoot the problem better.

yes it was telling me that the error is in my module...which track open and closing the forms..

now i delete all the events i have on on open and close with that code so the error dissapared.

now i have to find the solution of the problem of that who modules. i know where is but it seems that the syntax is not correct.

i will still searching to see if i find similiar problems
thank you for your help
i will be back again soon to ask you experts

 

[lacampeona]

Hello Gasman

Have you tried googling? as it appears you just need the opendynaset with dbseechanges?
To get an empty rs just use where 1=0 with a select statement.

can you show me some example how to implement that? some clue?
thank you

 

[isladogs]

IIRC, each argument should be separated by commas
e.g. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Are you sure you need all three arguments. Perhaps omit dbAppendOnly.
Otherwise, try:
Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbSeeChanges, dbAppendOnly)

EDIT:
But looking at some old code of mine, I used the OR operator in some cases. So perhaps it should be
Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbSeeChanges OR dbAppendOnly)

 

[lacampeona]

Hello Isladogs
I dont know what is wrong today but no is not correct.
it says me invalid procedure error 3001
hmmmm

 

[isladogs]

I edited my post. Please review my updated comments.

Normally I only use 2 arguments:
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

 

[Gasman]

Hello Gasman



can you show me some example how to implement that? some clue?
thank you

I just googled 'dbseechanges' and this was the first link?

https://www.google.com/amp/s/gunnarpeipman.com/you-must-use-the-dbseechanges-option-with-openrecordset-when-accessing-a-sql-server-table-that-has-an-identity-column/amp/

 

[June7]

Normally I only use 2 arguments:
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Works for me.

 

[lacampeona]

Hello Isladogs
yes now starts working yeess thank youuuu

i use this code. thank you very much.

EDIT:
But looking at some old code of mine, I used the OR operator in some cases. So perhaps it should be
Set rs = DBEngine(0)(0).OpenRecordset("tblLogDoc", dbOpenDynaset, dbSeeChanges OR dbAppendOnly)

if i can continue this thread...i just notice that also my audit trail is gone...is not tracking nothing

here is the code ...i think again is the problem with recordset? access say no error, but i notice now that is now working...it stopped wrtiting.
do you think
this line is also the problem?

Set rst = db.OpenRecordset("select * from tblAuditTrail", adOpenDynamic)

Option Compare Database
Option Explicit

Public Function AuditChanges(RecordID As String, UserAction As String)
On Error GoTo auditerr

Dim db As Database
Dim rst As Recordset
Dim clt As Control
Dim UserLogin As String

Dim Kdo As String

Kdo = TempVars!UserName


Set db = CurrentDb
Set rst = db.OpenRecordset("select * from tblAuditTrail", adOpenDynamic)

UserLogin = GetUserLogin()
Select Case UserAction
Case "new"
With rst
.AddNew
![DateTime] = Now()
!UserName = Kdo
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
.Update

End With

Case "Delete"
With rst
.AddNew
![DateTime] = Now()
!UserName = Kdo
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
.Update
End With
Case "Edit"
For Each clt In Screen.ActiveForm.Controls
If (clt.ControlType = acTextBox _
Or clt.ControlType = acComboBox) Then
If Nz(clt.Value) <> Nz(clt.OldValue) Then
With rst
.AddNew
![DateTime] = Now()
!UserName = Kdo
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
!FieldName = clt.ControlSource
!OldValue = clt.OldValue
!NewValue = clt.Value
.Update
End With
End If
End If
Next clt
End Select
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

auditerr:
' MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error"
Exit Function

End Function

 

[lacampeona]

Hello experts
i just resolve my audit trail,...i just put again dbSeeChanges
it seems that today Access is in love with that dbSeeChanges....

Set rst = db.OpenRecordset("select * from tblAuditTrail", adOpenDynamic,dbSeeChanges)

can i ask another question....i am totaly new now with having ms access database on sql server...and now i am testing and looking and find some errros that now are resolved....i start thinking....that putting database on server is telling me now the truth how bad is my database?
it start with so many errors....
now i have another error

when trying to open record id...i have marked it like hyperlink...and then when i open the form with selected id...when i clik it say me

ODBC - call failed. (Error 3146)​

i have all the form in the same way...and i get this error on 3 forms from 4 forms....
maybe you experts know what this mean??
i read that maybe is connection problem..i close i open and i still get error on these form...the form open but is calling me that errorr
i dont like that....
hmmmmmmm

 

[isladogs]

You ALWAYS need dbSeeChanges for any recordset used to edit a SQL linked table (i.e. UNLESS its dbOpenSnapshot)

I've never used adOpenDynamic BUT as the name implies that is used with ADO recordsets ADO Open Method (w3schools.com)
I believe you are using DAO recordsets not ADO

 

[lacampeona]

thank you very much for your help and good explanation
i still have to learn so much
thank you
now i have to resolve another error...

ODBC - call failed. (Error 3146)​