I am using ADO in VB6 to open 3 Access databases. Data from several tables in two of the databases will be used to create and populate a new table in the third database. I want to do this all this with SQL statements and then use Execute method to run the SQL.

Originally we did this all in one database by copying tables, but have decided not to go that route (for reasons not relevant here).

Is it possible to reference tables in different databases in the SQL statement? If so, what is the syntax to refer to specific databases in the SQL? I assume I refer to the connection object somehow, but I cannot find the information anywhere.

Example (that might not be quite correct) - our actual SQL is long and complicated due to the nature of the data:

SELECT alias1.field1, alias1.field2, alias1.field3, alias2.field1, alias2.field2 INTO MyNewTable
FROM tblOne alias1 INNER JOIN tblTwo alias2 ON alias1.field1=alias2.field1

tblOne is from the first database and tblTwo is from a second database. MyNewTable will be in a third database.

If that is not possible, is there a way, again with SQL statements, to perform the 'Get External Data' available within Access' File menu to 'Link Tables' and perform the same operations?

Thanks,
Jim

I am not an Access person, but I have done a little with it. I believe that you can link the tables in the first two DB's into the third (Import Data > Link Tables). This may allow your VB code to only talk to the 3rd DB, but see the tables in the first two. I am not sure if you can see linked tables via ADO, but if you can, I think this will be your best bet.

John Lick
JohnRLick@hotmail.com

John R Lick
JohnRLick@hotmail.com

Thanks for the reply. All this has to be done through VB code and SQL (we are just connecting to Access databases) since the program will be run by users with their own databases.

Jim

Jim
Try the following

SELECT alias1.field1, alias1.field2, alias1.field3, alias2.field1, alias2.field2 INTO db3.dbo.MyNewTable
FROM db1.dbo.tblOne alias1 INNER JOIN db2.dbo.tblTwo alias2 ON alias1.field1=alias2.field1

db1, db2, and db3 are your database names

Niaz

Jim

My previous reply works for SQL Server database. For Access databases you may need a modified syntax. Sorry for the confusion.

Niaz

You could try this, it should work with the Access database


SELECT alias1.field1, alias1.field2, alias1.field3, alias2.field1, alias2.field2 INTO MyNewTable
FROM tblOne alias1 IN "db1" INNER JOIN tblTwo alias2 IN "db2" ON alias1.field1=alias2.field1

db1 and db2 are the path to access your database
the sql is execute on the 3 your destination database

Stéphane