I am just starting to work in VB/Access, coming from a VFP environment. I am trying to use a Recordset (like I would use a Cursor, i.e. "CREATE CURSOR...", in VFP) as a temporary, updateable workspace -- instead of using Access tables which are prone to bloat and would need periodic maintenance. However, I am getting "Run-time error '-2147217887...' Multiple-step operation generated errors..." when any of the !DaysAhead assignments in the simplified code below are executed. The Recordset is getting the data from the SELECT fine, but it appears that it is not updateable. In this instance, I only want to use it as a scratch space to hold processed data for subsequent reporting (as opposed to using it to ultimately update the source data).

What am I doing wrong? Is there a better way to accomplish this without resorting to using Access tables. By the way, I am working in the Access environment because this is a legacy Access application that has to be fixed in short order; otherwise, I would be inclined to rewrite it to use strictly VB.

Any insight would be greatly appreciated. Thanks, Scott

Dim cnForecast As New ADODB.Connection
Dim rsForecast As New ADODB.Recordset
Dim strForecastConnect As String
Dim strSQL As Variant

strForecastConnect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=PALSQL;Mode=ReadWrite;Initial Catalog=Calls"
cnForecast.CursorLocation = adUseClient
cnForecast.Open strForecastConnect

' Get list of accounts and corresponding related info to process
strSQL = SELECT * FROM a LEFT JOIN b ON b.id1 == a.id1"
rsForecast.CursorLocation = adUseClient
rsForecast.Open strSQL, cnForecast, adOpenForwardOnly, adLockOptimistic

' Loop through accounts and set defaults as necessary
With rsForecast
While .EOF = False
If !DaysAhead <> Null Then
!DaysAhead = 14
ElseIf !DaysAhead < 7 Then
!DaysAhead = 7
ElseIf !DaysAhead > 50 Then
!DaysAhead = 50
End If
.MoveNext
Wend
End With