What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleJoin tables in two different databases using an ADO join
KeywordsADO, join, two databases, multiple databases
At design time, open the Project menu and select References. Find the "Microsoft DAO 3.6 Object Library" entry (or whatever version you have) and check it. Then click OK.

Next create a Data control and controls to bind to it. At run time, connect the Data control to database 1. Then create a query of the form:

    SELECT * FROM Table1, [;Database=database2].Table2

In this example, the People table is contained in the database connected directly to the Data control. The database location may differ on your system but the final query on my system is:

    SELECT * FROM People, 
    WHERE People.LastName  = Salaries.LastName
      AND People.FirstName = Salaries.FirstName

Note that the join columns FirstName and LastName are ambiguous so you need to tell the database engine which versions to use. That is done here by prefixing the column names by the table names People and Salaries.

For the same reason, you need prefix the column names with their tables when you set the DataField properties of bound controls. In this program, the txtLastName TextBox's DataField property is set to People.FirstName. The Salary field is only contained in the Salaries table so the txtSalary TextBox's DataField property is set to Salary.

This HowTo was inspired by an answer posted by Suresh at the VB Helper Questions Page.

See also Join tables in two different databases using linked tables. The ADO join method described here is easier if you just want to perform this one operation and you don't want to leave a link lying around. The linking method is simpler, particularly if you want to leave the link in the database for later use possibly in other programs.

For information on database programming using ADO.NET (in VB .NET), see my book Visual Basic .NET Database Programming.

Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.