Error You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Se

[KEKeogh]

Morning!

I had a database which I have recently switched over to use the data from a SharePoint site. On the one form I had VBA to add an entry to the table it looks up from in the entry is not in the list (code below) and it was working fine.

However after making the switch to the SharePoint linked tables I get an error "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column"

Now I know it goes in the initial code somewhere but where?

Thanks
Kathie

*************************************

Private Sub Parent_Company_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler

' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf

' database and recordset object variables
Dim db As DAO.Database
Dim rs As DAO.Recordset

' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the current database
Set db = CurrentDb
Set rs = db.OpenRecordset("Access_Parent_Company")
' using the recordset object
With rs
.AddNew ' prepare to add a new record
.Fields("Access_Parent_Company") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset object
End With
Response = acDataErrAdded ' confirm record added
Else
Me.Parent_Company.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If

Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub

 

[KEKeogh]

OH and the table is linked by an ODBC Database link through SQL

 

[Minty]

Around about here;

open a connection to the current database
Set db = CurrentDb
[COLOR="Red"]Set rs = db.OpenRecordset("Access_Parent_Company", dbSeeChanges)[/COLOR]
' using the recordset object
With rs

 

[KEKeogh]

Now I get "Error #3001: Invalid argument"

 

[Minty]

The syntax is
.OpenRecordset(Name, Type, Options, LockEdit)
https://msdn.microsoft.com/en-us/library/office/ff820966.aspx

 

[KEKeogh]

I apologize for my ignorance but then what is dbSeeChanges considered?

I am a novice when it comes to code. What I have working I got from the internet and most likely all of you.

I pasted it exactly like you had it.

Kathie

 

[Minty]

Okay apologies - your code suggested a good grasp of what you were dealing with.
So I think you will need to use

Set rs = db.OpenRecordset("Access_Parent_Company",dbOpenDynaset, dbSeeChanges)

 

[KEKeogh]

THANK YOU SO MUCH!!!!!

Sorry for the confusion. Should have put me being novice in my original post. Gains from the internet can definitely give the illusion of being an expert.

That last bit of code got me closer but was still getting a different error but turns out I had a table name in one spot that was supposed to be a field name.

Now with the right dbSeeChanges code and that fix, it works perfect!!!

 

[Minty]

No Problem - glad it's sorted.