Querying across Multiple Databases

[Brad Autry]

I'll try to keep this as brief as possible.

I am working with "one" massive database that, due to size, has been broken
down into 37 separate databases. I put together a front end and linked the
tables from which I need data pulled.

I am essentially working with five tables, however they have been broken
down as follows:

1.) Requisition table - one table/database
2.) Candidates Table - one table/database
3.) Response Table - segmented into 10 separate database/tables
4.) Form table - segmented into 2 separate database/tables
5.) Status table - segmented into 2 separate databse/tables

Again, I am not able to consolidate any of the tables that were broken down.

That being the case, what methods are there available to me in order to
query data? I am not certain how/if I can place all these linked tables into
one query, nor am I certain of what SQL code or anything that would enable me
to pull this off. The only thing I can think of is using the same selection
criteria and creating multiple queries with the various tables to try to
cover everything (which is completely impractical) and seemingly prone to
missing out on something.

Any suggestions would be greatly appreciated.

Thank you in advance.

 

[TedMi]

Offhand, I'd say your database exceeds the comfort zone of Access. You will
be much better served by a client-server DB such as MS SQL Server.
-TedMi

 

[John Spencer MVP]

If it is that massive then you probably need to invest in a major database
engine such as MS SQL or Oracle to hold the data. You could still use Access
as a frontend to the data AS LONG AS you never exceeded the limit of 2
gigabytes of data when you where running your queries.

With the structure you have outlined you might be able to return the
information but depending on how the data is segmented and the relationships
between the tables you could need 40 different queries to return the data you
might want that would be handled by one query if the all the data was in one
database and 5 tables instead of 16 (or more) tables in 5 different databases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Brad

How is it that your database(s) have gotten so large? Have you backed up
your files recently, then run Compact & Repair?

Define "massive" -- it isn't at all uncommon to have data in the 50-100
Mbyte range.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brad Autry]

Brad

How is it that your database(s) have gotten so large? Have you backed up
your files recently, then run Compact & Repair?

Define "massive" -- it isn't at all uncommon to have data in the 50-100
Mbyte range.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The database, in total, is roughly 50 gigs. I was a bit taken aback by its
size, needless to say. It is a historical database dating back 5 years or so
of applicant data for a large company. I need to pull data for specific work
locations over given periods of time. I'm investigating what options I have,
in regards to moving the data to a SQL or Oracle server here, as running even
one of the 40+ queries I need to run to yield one selection of data is taking
upwards of 10 minutes to run. Taking roughly 6 hours to get the equivalent
of one query's worth of data is not something I'm looking forward to.

I am also trying to figure out how to use variables for the selection
criteria to at least somewhat streamline this process.

Thanks to everyone for your feedback. Wish me luck!

 

[Jeff Boyce]

Brad

Whooo! 50 Gbytes is quite a database. Any chance images are being stored?

Given that size, I'd be looking into SQL Server or ...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP