error 3622

L

Legacy 15162

Guest
Below is my code. I am getting error 3622 "You must use the dbSeeChanges option with open.recordset when accessing a sql server table that has an identity column.

any idea what to change?



Dim rsCER As DAO.Recordset
Dim rsMsr As DAO.Recordset
Dim strCER As String
Dim sqlstr As String
Dim lngStore As Long
Dim lngCER As Long
Dim i As Integer

sqlstr = "SELECT dbo_Measure.*" & _
" FROM dbo_Measure" & _
" WHERE dbo_Measure.Storeid not in(12254,14482,14483) AND dbo_Measure.Storeid >10000 AND dbo_Measure.Type = ""CHAMPS Excellence Review""" & _
" ORDER BY dbo_Measure.Storeid asc, dbo_Measure.Year desc"

Set rsMsr = CurrentDb.OpenRecordset(sqlstr)

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete From tblCERTemp"
DoCmd.SetWarnings True

Set rsCER = CurrentDb.OpenRecordset("tblCERTemp")

rsMsr.MoveFirst

While Not rsMsr.EOF
If lngStore <> rsMsr!StoreID Then
rsCER.AddNew
lngStore = rsMsr!StoreID
lngCER = 0
Else
If lngCER <> 0 Then GoTo nextmsrrecord
End If

For i = 13 To 1 Step -1
lngCER = rsMsr.Fields("Period" & i)
If lngCER <> 0 Then
rsCER!Store = lngStore
rsCER!Year = rsMsr!Year
rsCER!Type = rsMsr!Type
rsCER!LatestCER = lngCER
rsCER!Period = i
rsCER.Update
GoTo nextmsrrecord
End If

Next i

nextmsrrecord:

rsMsr.MoveNext

Wend
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
L

Legacy 15162

Guest
instead of using dao.recordset, i utilized adodb.recordset and was able to fix the problem.

if anyone knows how to fix this problem using dao, could you still post?
 

Forum statistics

Threads
1,148,049
Messages
5,744,509
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top