error 3622

[Legacy 15162]

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

 

[Legacy 15162]

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?