Locate unmatched records from many similiar Access Databases

[rfranzl]

Hello,

I need some help, I have about 200 databases that are copies of an original database that has a similiar table in all of the databases, called "tblCodebook".

What I am trying to do is to create a macro that will go into each of the 200 databases, run an identical "unmatched query" to find any records that might be in one of the copies of the "tblCodebook". There is a shared primary key in the "tblCodebook" which is a StoryID and EvaluatorID which together is unique.

Here is my query:
SELECT tblcodebook1.StoryID, tblcodebook1.EvaluatorID
FROM extracodebook LEFT JOIN tblCodebook ON (extracodebook.EvaluatorID
= tblCodebook.EvaluatorID) AND (tblcodebook1.StoryID =
tblCodebook_master.StoryID)
WHERE (((tblCodebook_master.StoryID) Is Null) AND
((tblCodebook_master.EvaluatorID) Is Null));

I can get the query to run in one database of course, but I am having problems trying to write a macro that does connections to each of the 200 databases that I have so I can run this query in it.

I think an alternative is to try and get all of the "tblCodebook" tables into one database, then run the query, but I would like to somehow write some macro to do the connections to the databases programatically so I do not have to compile all of these tables I wish to query into one database.

I am fluent in NI LabView, C++, and a little of VB.

Please help! Any help would be greatly appreciated!!!!!!

[rfranzl]

Sorry, here is my correct query, had a mistype...

SELECT tblcodebook1.StoryID, tblcodebook1.EvaluatorID

FROM tblcodebook1 LEFT JOIN tblCodebook_master ON (tblcodebook1.EvaluatorID = tblCodebook_master.EvaluatorID)
AND (tblcodebook1.StoryID = tblCodebook_master.StoryID)
WHERE (((tblCodebook_master.StoryID) Is Null) AND
((tblCodebook_master.EvaluatorID) Is Null));


my tables for this query are tblcodebook1 and tblcodebook_master

[nico5038]

Sorry, here is my correct query, had a mistype...

SELECT tblcodebook1.StoryID, tblcodebook1.EvaluatorID

FROM tblcodebook1 LEFT JOIN tblCodebook_master ON (tblcodebook1.EvaluatorID = tblCodebook_master.EvaluatorID)
AND (tblcodebook1.StoryID = tblCodebook_master.StoryID)
WHERE (((tblCodebook_master.StoryID) Is Null) AND
((tblCodebook_master.EvaluatorID) Is Null));


my tables for this query are tblcodebook1 and tblcodebook_master

You could create a giant UNION query like:

select "db001" as Origin, StoryID, EvaluatorID from tblcodebook1 IN C:\VB\Codebook1.MDB
UNION
select "db002" as Origin, StoryID, EvaluatorID from tblcodebook1 IN C:\VB\Codebook2.MDB
UNION
...etc...
 

Becuase of the Origin field constant you can check where the values are available or not.

Getting the idea ?

Nic;o)