Sql dbSeeChanges error

[dullster]

We just migrated an Access 07 db to SQL for testing. When opening a form with
a sub form I am getting this error.
Run-time error '3622': You must use the dbSeeChanges option with
OpenRecordset when accessing a SQL Server table that has an IDENTITY column.
When I click Debug, this is the VBA that presents.


Private Sub Form_Load()
Dim cdb As Database
Dim rsParams As Recordset
Set cdb = CurrentDb
Set rsParams = cdb.OpenRecordset("SELECT * FROM app_parameters WHERE
param_name = 'base_directory'")
base_dir = rsParams("param_value")


If Not IsNull(Me.OpenArgs) Then
Dim strArgArray
strArgArray = Split(Me.OpenArgs, "|")
Select Case strArgArray(0)
Case "newSPfromWI"
DoCmd.GoToRecord , , acNewRec
Dim rs As Recordset
Set rs = cdb.OpenRecordset("SELECT * FROM [Customers Table]
WHERE CustomerID = " & strArgArray(1))
With Me
!CustomerID = rs("customerid")
!LastName = rs("lastname")
!FirstName = rs("firstname")
!Customeraddress = rs("mailingaddress")
!Customercity = rs("city")
!Customerstate = rs("state")
!Customerzip = rs("zip")
!PhoneNumber = rs("phonenumber")
![Parcel#] = strArgArray(2)
!Township = strArgArray(3)
!Town = strArgArray(4)
!Range = strArgArray(5)
!Qtr = strArgArray(6)
!Qtr2 = strArgArray(7)
!CountyAddress = strArgArray(8)
!Roadway = strArgArray(9)
!Bedrooms = strArgArray(10)
!DateIssued = strArgArray(11)
!PriorPermitDate = strArgArray(12)
End With
Set rs = Nothing
Case Else
End Select
End If
End Sub

I have tried several codes with no success. Any ideas?

 

[Jeff Boyce]

Check your OpenRecordset syntax in Access HELP. There are optional
parameters in that command, one of which pertains to whether the recordset
can "see changes" in the underlying data set.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[dullster]

I believe I may have figured out where the change needed to be.

 

[Jeff Boyce]

Care to share?

Others may be looking for the same solution...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I believe I may have figured out where the change needed to be.

We just migrated an Access 07 db to SQL for testing. When opening a form
with
a sub form I am getting this error.
Run-time error '3622': You must use the dbSeeChanges option with
OpenRecordset when accessing a SQL Server table that has an IDENTITY
column.
When I click Debug, this is the VBA that presents.

Private Sub Form_Load()
Dim cdb As Database
Dim rsParams As Recordset
Set cdb = CurrentDb
Set rsParams = cdb.OpenRecordset("SELECT * FROM app_parameters WHERE
param_name = 'base_directory'")
base_dir = rsParams("param_value")


If Not IsNull(Me.OpenArgs) Then
Dim strArgArray
strArgArray = Split(Me.OpenArgs, "|")
Select Case strArgArray(0)
Case "newSPfromWI"
DoCmd.GoToRecord , , acNewRec
Dim rs As Recordset
Set rs = cdb.OpenRecordset("SELECT * FROM [Customers
Table]
WHERE CustomerID = " & strArgArray(1))
With Me
!CustomerID = rs("customerid")
!LastName = rs("lastname")
!FirstName = rs("firstname")
!Customeraddress = rs("mailingaddress")
!Customercity = rs("city")
!Customerstate = rs("state")
!Customerzip = rs("zip")
!PhoneNumber = rs("phonenumber")
![Parcel#] = strArgArray(2)
!Township = strArgArray(3)
!Town = strArgArray(4)
!Range = strArgArray(5)
!Qtr = strArgArray(6)
!Qtr2 = strArgArray(7)
!CountyAddress = strArgArray(8)
!Roadway = strArgArray(9)
!Bedrooms = strArgArray(10)
!DateIssued = strArgArray(11)
!PriorPermitDate = strArgArray(12)
End With
Set rs = Nothing
Case Else
End Select
End If
End Sub

I have tried several codes with no success. Any ideas?

 

[dullster via AccessMonster.com]

I do. I have pasted in the lines were I inserted the codes so you can follow
it. I had to so it to several forms in the same place. It is the
dbOpenDynaset, dbSeeChanges codes.

Private Sub Form_Load()
Dim cdb As Database
Dim rsParams As Recordset
Set cdb = CurrentDb
Set rsParams = cdb.OpenRecordset("SELECT * FROM app_parameters WHERE
param_name = 'base_directory'", dbOpenDynaset, dbSeeChanges)
base_dir = rsParams("param_value")

Care to share?

Others may be looking for the same solution...

I believe I may have figured out where the change needed to be.

[quoted text clipped - 52 lines]