Using ADO Recorsets in sql SELECT sentence

[Guest]

Hi,

I'm very new in SQL and VBa, so maybe it's very simple, but I'm not being
able to use one or more Recordsets in a SQL SELECT sentence.

Having 2 recordsets (opened) what I need is to relate them in a sql
populating a new recordset....
....
rsOne
rsTwo

ssql="SELECT rsOne!Field1,rsOne!Field2,rsTwo!Field1 FROM rsOne,rsTwo WHERE
rsOne!Field1=rsTwo!Field1"
rsThree.open cnn,,,adcmtext

But this gives me an error saying Microsoft Jet can't find rsOne object.

What I'm doing wrong?

Thanks for your help,

 

[Guest]

If you're using MS Access, or if you can use MS Access on your data just to
find the error, try using the graphical tools to do what you want to do and
then review the SQL that it creates. If you right-click the down arrow by
the View button in the upper right, one of the choices should be SQL View.

Sometimes it is not possible to use the graphical tools to create what you
want, and in that case my suggestion won't help you, but what you're
describing looks like a Select query relating two tables, and that can be
done using the graphical tools.

Hope this helps.

Keith

 

[Guest]

Keithlo ,

Thanks for your answer. In fact, I'm working over FoxPro data tables, in
different folders, and this (AFAIK) makes unfeasible your point.

So far, I decided to create new recordsets hopping I could be able to relate
them in a single new sql sentence. But I can't find out how to referenciate
these recordsets in the sql string sentence. If possible...(I guess it is..)

Regards,

 

[Tim Williams]

ADO doesn't work like that: recordsets can't be queried using SQL as though
they were tables.
You need to get your records into tables in the same database (or you could
dump them into named ranges in a worksheet...)

Tim