SQL using tables from two MDBs

[tedsmith]

I want to run a recordset based on an unmatched query to find new Routes in a bus timetable not yet entered in a master Routenames table.

This is because the actual route names do not appear in the timetable, only a route number (Route) and a destination code (Code). When browsing the timetable, the names are found only by looking up the code in the Routenames table.

The Timetable table is in a different database to the Routenames table.

This is easy when the two tables are in the one database using the standard unmatched query wizard in MSAccess to create the JOIN code. But in this case they are in two different databases.
The GROUP is used to only show the new route once as it might appear many times in a new timetable.

Problem is how do I refer to tables in two different databases in the one query?

I can do it with 2 databases when inserting INTO a table with IN but this does not seem to work in the above case.

Example of Unmatched query with the 2 tables in 1 database:-

SELECT Timetable.Route, Timetable.Destination
FROM Timetable LEFT JOIN RouteNames ON Timetable.Code = RouteNames.Destination
GROUP BY Timetable.Route, Timetable.Code, RouteNames.Destination
HAVING (((RouteNames.Destination) Is Null));

 

[Bluejay07]

Hi Ted,

Have you tried using a structure similar to:

select *
from dbo.ThisTable t1
join ThatDatabase.dbo.ThatTable t2
on t1.KeyColumn = t2.KeyColumn

If at first you don't succeed, then sky diving wasn't meant for you!

 

[tedsmith]

Pardon my ignorance but could you please explain the use of dbo?

Would I Dim t1 & t2 as tabledef or something else?

By dbo.ThisTable is this the same as C:\MyFirstFolder\MyFirstDatabase.mdb.MyFirstTable t2?

and is ThatDatabase.dbo.ThatTable t2 the same as C:\MySecondFolder\MySecondDatabase.mdb.MySecondTable t2 ?

 

[gmmastros]

If you were using Microsoft SQL Server then bluejay07's advice would make more sense. I haven't used Microsoft Access in a long time so I cannot offer any good advice. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Thanks George.
Sorry TedSmith. I did not notice your reference to Access. And to answer your question, dbo stands for Batabase Owner when referencing tables in Microsoft SQL Server.

There is also a VBA forum that might provide more details.
forum707

If at first you don't succeed, then sky diving wasn't meant for you!

 

[tedsmith]

I'm not using MSAccess except that the tables were originally created using it.

I am using VB6 opening a snapshot recordset with a SQL type statement and finding unmatched records in two tables in two different databases.

If I were using msaccess I would simply link one database to the other but how can I do this in vb6? The SQL produced by a similar msaccess query is the same as though the linked table is a local table.

The dbo thing doesn't seem to work in vb6.

 

[jadams0173]

If you linked the tables into one of the databases wouldn't unmatched query wizard then be able to run? Even though one of the tables will be linked it is still in the "same" database?

One way I've done this in the past, which may not be the best solution, is to query each database and have two recordsets. Then I loaded both rs into a dictionary object and used the exists method to see if the key was already in the dictionary, thus removing duplicates.

In your case maybe you could load the table that contains the new routes into the dictionary and the use the master table to remove the ones that are already in the dictionary. When you are done, whatever is in the dictionary is routes that don't exist in the master table.

 

[tedsmith]

I can run a simple vb6 select query in one recordset on data from another database table by using IN C:\OtherFolder\Otherdatabase/mdb but this doesn't seem to work for a group query.

I suspect I don't have the right combinations of syntax.