>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


Hi,

I use code below to create an updateable recordes and populate it programmatically.

    Set rstResults = New ADODB.Recordset
    With rstResults
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Fields.Append "Name", adChar, 37, adFldIsNullable
        .Fields.Append "Birthdate", adChar, 10, adFldIsNullable
        .Fields.Append "Phone", adChar, 10, adFldIsNullable
        .Fields.Append "City", adChar, 30, adFldIsNullable
        .Fields.Append "State", adChar, 2, adFldIsNullable
        .Open
    End With

HTH,