469,646 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

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

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!!!!!!
Jul 24 '07 #1
1 1806
Lysander
344 Expert 100+
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
Jul 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Stephen | last post: by
2 posts views Thread by MichaelH | last post: by
3 posts views Thread by Dennis | last post: by
2 posts views Thread by trihanhcie | last post: by
9 posts views Thread by smartbei | last post: by
2 posts views Thread by rookiejavadude | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.