ADO recordset not recognized by DoCmd.RunSql ...

[MichaelRed]

I have generated two ADODB recordsets ("tblChargeNbr" and "rsChargeNbr" as ADODB recordsets. Both are 'found/present' as I can see values & / or properties of the recordsets. I have a "Query" to insert the [rsChargeNbr] records into [tblChargeNbr].

DoCmd.RunSql strUpDtSQL

returns an error:
"... Cannot find Table or Query 'rsChargeNbr' ... "

Placing a breakpoint on the DoCmd.RunSql, I "see" the err when "stepping to the next command. Then I switch to the immediate/debug window and"

? rsChargeNbr!chgNbr
950X00
? tblChargeNbr!ChgNbr
? tblChargeNbr.Bof, tblChargeNbr.Bof
True          True

? sqlUpDt(Idx)
'INSERT INTO tblChargeNbr 
'( ChgNbr, ChgDesc, XGMU, BillRate, PFA, 
'TemplateFlag, TimeFlag, ExpFlag, TypeCode ) 
'SELECT rsChargeNbr.ChgNbr, rsChargeNbr.ChgDesc, rsChargeNbr.XGMU, 
'rsChargeNbr.BillRate, rsChargeNbr.PFA, rsChargeNbr.TemplateFlag, 
'rsChargeNbr.TimeFlag, rsChargeNbr.ExpFlag, rsChargeNbr.TypeCode 
'FROM rsChargeNbr

So, what gives?

rsChargeNbr certainly appears to be in scope, as I can get values from it. While APPARENTLY not relevant, tblChargeNbr also appears to be in scope, as I can get the properties of BOF & EOF from it (it is - because I cannot get the query to work) currently an empty table.

The only 'new' issue is that reChargeNbr is an ADODB recordset created form an external source:

        Set cmdCreRs = New ADODB.Command
        With cmdCreRs
            .ActiveConnection = Conn
            .CommandType = adCmdText
            .CommandText = strSQL
            .Execute lngRowsChanged     
        End With

        '  othher stuff - omitted


                Set rsChargeNbr = New ADODB.Recordset
                rsChargeNbr.Open cmdCreRs

tblChargeNbr is created in a analogous manner, except that the connection is 'activeproject.connection'

Some thoughts ..... Please?





MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over