Open multiple databases

[boycie]

Hi

I would like to open several databases (up to 200). These have exactly the same tables. I would like to run a query on each database and append the data to another table in my new database ie.

loop through databases
run query A on table A
Append to table B in my new database
next

This will give me a central table with all required data.

Can anyone guide me in the right direction for this

thanks

 

[jdraw]

First question I have is Why do you have 200 or so databases with the same structure?
What is the real requirement?

 

[boycie]

I have just started work for a company with 200 satellite offices running a client db

 

[DCrake]

I had a similar situation with a national franchise with nearly 60 sites. The solution we came up with was that each site send a extract to our server overnight and the host db imported all the extracts into its main table then run the queries on that.

 

[boycie]

Yes, we currently get a datafile sent in once a month and analysis is done on each one.

You mentioned the host db importing the extracts - was that Access? How did that work?

 

[DCrake]

Each franchisee sent two files each night, Sales transactions and stock tables exported as txt files and zipped up. They were sent to our central server which trawled a known location at a gien time slot. Five mins after each slot Access lookd for the known file, if found it would unzip it and import the txt files into holding tables, then validated and then appended into the live table.

As far as the sales transactions were concerned we were talking about 500 sales per day, 6 days per week, 52 weeks per year across 56 sites.

 

[jdraw]

David,

I would expect you had some control processes to ensure each site's files were received, processed etc. For Boycie's sake, I'm just suggesting some discipline and logging of transactions is a critical part of such processing.

 

[DCrake]

Our biggest problem was that the epos system that created the zip files was not ours but a third party. And yes we did have stringent processes in place to ensure that each site's data was received, otherwise an exception report was created and was handled manually the next day.

 

[boycie]

Thanks for your replies. I've got a lot to think about. I like the idea of importing the databases and running analysis on it after. I'm thinking of maybe SQL Server doing this by creating an import job for each db.

 

[DCrake]

If you have SQL server you may be able to DTS to get the data from the satelite sites, that is of course they can connect to each site.

Also depending on the number of transactions SQL will be more robust.