Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
|
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 24th, 2003, 09:20 AM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Refer to multiple databases in one SQL statement

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
 
Old June 24th, 2003, 09:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

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
 
Old June 24th, 2003, 11:44 AM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 24th, 2003, 11:56 PM
Authorized User
 
Join Date: Jun 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 25th, 2003, 12:13 AM
Authorized User
 
Join Date: Jun 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jim

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

Niaz
 
Old June 25th, 2003, 08:57 AM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Multiple databases...multiple problems keady2 BOOK: Access 2003 VBA Programmer's Reference 1 June 7th, 2006 01:52 PM
Multiple Hierarchical Databases Macsood Oracle 0 December 6th, 2005 12:47 PM
multiple sql statement IbrahimSyed SQL Server 2000 5 July 1st, 2005 10:27 AM
Statement for Multiple Filters in SQL mckly Pro VB Databases 1 August 2nd, 2004 01:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.