HELP NEEDED! Trying to do a unmatched query on many different databases with a macro

[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 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


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!!!!!!

[Lysander]

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 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


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!!!!!!

Hi, I have just answered a problem above, and I think the same solution would solve your problem

What you need to do is write some code that would loop through all your databases and drop the table tblCodeBook, then re-create a link to the same table in the next database. The article below (not written by me) details how to create a link to a table

http://www.alvechurchdata.co.uk/acclink.htm