SELECTING TABLES & PATHS

[Guest]

actually it's a repost, trying to improve last message title ...

When performing a SELECT how do I tell my tables are located in different
paths?

I'm doing:
rCON.Open sSQL_C, cnn, , adCmdText.

where
cnn="cnn.Open "driver={Microsoft dBase Driver (*.dbf)};" & _
"driverid=277;dbq=c:\VB\CBS"

sSQL_C="SELECT TABLE1.field1,TABLE1.field2,TABLE2.field1,TABLE2.field2 FROM
TABLE1,TABLE2 WHERE TABLE1.field1=TABLE2.field1 AND ((TABLE1.field1 like
'YL%'))"

TABLE1 is located in c:\VB\CBS
TABLE2 is located in c:\VB\CBS\NP

Any clue?
TIA

 

[Tim Williams]

I'm not sure you can open two different database files at once using ADO...

Tim

 

[Guest]

Do you mean in different paths? - I suppose- because no problem within same
path...

Cheers,

 

[NickHK]

You can use 2 dbf tables <files> from the same database <folder> with
ADO/DAO, but each connection is only to one DB, so you cannot achieve you
aim directly.
You can create a connection for each DB and an RS for each connection.

Or you link both files in an Access DB and query them from there.

NickHK

 

[Guest]

2 tables it's a narrow limitation...

Right now I do have 3 different recordsets (one for each sql to each table).
rART1.Open sSQL_1, cnn, , , adCmdText
rART2.Open sSQL_2, cnn, , , adCmdText
rART3.Open sSQL_3, cnn, , , adCmdText


What would be best option to get a new recordset having comlumns for each of
them?

Is it a possibility to do a new sql (from those recorsets) where I can see
all columns where table1.field1=table2.field1 and table1.field1=table3.field2

What code I need to invoke these recordsets?


Thanks for your help.

 

[NickHK]

Sharon,
Well, you can use more than 2, but you are limited to tables in the same
database.
Your initial post concerned different database, but now you are talking
about different tables in the same database, as your 3 x recordsets use the
same connection.
Which is it ?

Assuming your driver/provider supports JOINs then you can achieve your aim,
even though (IIRC) DBase is not relational in nature.
You need to check the SQL required to produce your results.

NickHK

 

[Guest]

Hi NickHK,

I need to relate 3 DBASE tables, located in different paths. I understand I
can't do this in just one sql command, so I write 3 sql that generates 3
recordsets. Next step is to relate these recordsets to a (new) recordset
which will populate excel cells. Then (last step) I need update tables when
cells change (or whne closing excel) back to tables.

Example, columns are:
table1: code,price,lastprice
table2: code,description,stock,cost
table3: code,units,idate,fdate


Need to populate excel cells with:

code,description,price,lastprice,stock,units .........

Does it make any sense to you?
Is it a better solution?

TIA,