One Form Linked to Multiple Databases

I have multiple databases that I'm working with (e.g.: every time an order is
created with a company specific program I'm using, a single database file is
created for that specific order), and there will be continuous additional
databases that will be created as new orders are created (all the same format
- just different data). I want to create a form that some how can pluck data
out of all, any single or any combination of these databases. Is this
possible?
The goal is to obtain data from all of the orders / databases.

On Tue, 4 Jan 2005 05:55:01 -0800, "Colin"
wrote:

I have multiple databases that I'm working with (e.g.: every time an order is
created with a company specific program I'm using, a single database file is
created for that specific order), and there will be continuous additional
databases that will be created as new orders are created (all the same format
- just different data). I want to create a form that some how can pluck data
out of all, any single or any combination of these databases. Is this
possible?
The goal is to obtain data from all of the orders / databases.

Do you mean new DATABASES? A new .mdb file container for multiple
tables, forms, reports, etc. every time you get a new order? That's
absolutely *dreadful* design.

Or do you mean new TABLES, within a database? That's not *quite* as
bad; it's just awful design, rather than dreadful.

You would be MUCH MUCH better off using Access as a relational
database, as it is designed, with one single Order table related one
to many to an OrderDetails table, as in the Northwind sample database.

Stop. Step back. Unless you work with Access instead of struggling
against it, you'll have no end of trouble!


John W. Vinson[MVP]

Thanks for your post.
Unfortunately, every time the company program completes an order it creates
a new database file for that specific order (i.e.: every order has its own
..mdb file). I'm trying to find a way to link all of the databases - including
new databases that will be continuously created with the filling of each new
order by the company, so that the data from each order can be viewed in a
report or form.
Any suggestions?


"John Vinson" wrote:


Do you mean new DATABASES? A new .mdb file container for multiple
tables, forms, reports, etc. every time you get a new order? That's
absolutely *dreadful* design.

Or do you mean new TABLES, within a database? That's not *quite* as
bad; it's just awful design, rather than dreadful.

You would be MUCH MUCH better off using Access as a relational
database, as it is designed, with one single Order table related one
to many to an OrderDetails table, as in the Northwind sample database.

Stop. Step back. Unless you work with Access instead of struggling
against it, you'll have no end of trouble!


John W. Vinson[MVP]

On Tue, 4 Jan 2005 10:11:03 -0800, "Colin"
wrote:

Thanks for your post.
Unfortunately, every time the company program completes an order it creates
a new database file for that specific order (i.e.: every order has its own
.mdb file). I'm trying to find a way to link all of the databases - including
new databases that will be continuously created with the filling of each new
order by the company, so that the data from each order can be viewed in a
report or form.
Any suggestions?

Fire the designer of this system. :-{(

If their assumption is that you can only view a report on data if the
database contains no other data, then whoever designed it knows
NOTHING about Access, and damn little about databases!

No, there's no good way to deal with this mess. You'll need to use the
VBA OpenDatabase method on each database, and you'll quickly run into
the Too Many Databases error. There will be NO simple way to combine
the data.

I would suggest, if you can't avoid this system, to do an end run
around it; create a database "under the radar" of the IT department or
whoever created this piece of junk, and have code in it to open each
database as it comes along, link to the table (presumably just one
table?) in it, and Append it into the normalized single database.

John W. Vinson[MVP]

Fire the designer of this system. :-{(

If their assumption is that you can only view a report on data if the
database contains no other data, then whoever designed it knows
NOTHING about Access, and damn little about databases!

No, there's no good way to deal with this mess. You'll need to use the
VBA OpenDatabase method on each database, and you'll quickly run into
the Too Many Databases error. There will be NO simple way to combine
the data.

I would suggest, if you can't avoid this system, to do an end run
around it; create a database "under the radar" of the IT department or
whoever created this piece of junk, and have code in it to open each
database as it comes along, link to the table (presumably just one
table?) in it, and Append it into the normalized single database.

John W. Vinson[MVP]
*************************
Thanks again.
I have talked to a few people who have a similar set up. Their answer was
they had to hire a guy to create a program that did what I believe you
suggested to do. That is not an option for me. I'm interested in trying to
code a new database as you suggested, however my programming experience is
pretty limited. Do you have any sample code I could use as a guide or know of
how I could find out some more on this particular subject?
Here is what I have to be able to do:
1. Some how be able to have the code go along and convert all of the
database files (1 per order) from access 97 to 2003.
2. Have this new database go along via code and pluck chosen data (e.g.: the
cost of a particular order or the cost of all of the orders in a particular
week) out of their individual databases and show the data in the new database
in a table. (Getting sort of confused here now).
I'm sure the coding behind such a task is out of my league - but if I knew
where to start I'd give it a go.

On Wed, 5 Jan 2005 04:33:01 -0800, "Colin"
wrote:

I have talked to a few people who have a similar set up. Their answer was
they had to hire a guy to create a program that did what I believe you
suggested to do. That is not an option for me. I'm interested in trying to
code a new database as you suggested, however my programming experience is
pretty limited. Do you have any sample code I could use as a guide or know of
how I could find out some more on this particular subject?
Here is what I have to be able to do:
1. Some how be able to have the code go along and convert all of the
database files (1 per order) from access 97 to 2003.
2. Have this new database go along via code and pluck chosen data (e.g.: the
cost of a particular order or the cost of all of the orders in a particular
week) out of their individual databases and show the data in the new database
in a table. (Getting sort of confused here now).
I'm sure the coding behind such a task is out of my league - but if I knew
where to start I'd give it a go.

Well, the good news is that little or no coding is necessary if you're
willing to do a brief manual process for each order. Some simple
coding would make the process shorter (in that the computer could do
much of the work). Access databases can do a LOT with no VBA code at
all.

It will not be necessary to convert any files, for one thing; A2003
can read data from A97 databases without difficulty.

What you would need to do is have one or more suitable tables set up
in your "master" database, and use File... Get External Data... Import
to import the data (probably all of it, unless there's a good reason
not to do so) from each Order database into your database's tables.
Perhaps you may need to use File... Get External Data... Link and run
Append queries instead in some cases. It just depends on the structure
of the data in the orders databases, and what information you need in
your database.

If you wish to pursue this option, it would help to know both the
structure (what tables, what fields, size, ...) of a typical Order
database, and whether you need to import a subset or all of this data.

John W. Vinson[MVP]

Well, the good news is that little or no coding is necessary if you're
willing to do a brief manual process for each order. Some simple
coding would make the process shorter (in that the computer could do
much of the work). Access databases can do a LOT with no VBA code at
all.

It will not be necessary to convert any files, for one thing; A2003
can read data from A97 databases without difficulty.

What you would need to do is have one or more suitable tables set up
in your "master" database, and use File... Get External Data... Import
to import the data (probably all of it, unless there's a good reason
not to do so) from each Order database into your database's tables.
Perhaps you may need to use File... Get External Data... Link and run
Append queries instead in some cases. It just depends on the structure
of the data in the orders databases, and what information you need in
your database.

If you wish to pursue this option, it would help to know both the
structure (what tables, what fields, size, ...) of a typical Order
database, and whether you need to import a subset or all of this data.

John W. Vinson[MVP]
**************************
Thank you for your help. I will try and get something up and running using a
master database. I believe some issues may arrise with the file size of the
master as each order's data is added (manually or otherwise). I tried the Get
External Data deal before, just trying to link 2 orders, bringing one into
another, and noticed that as all of the order databases are set up the same
(e.g.: the same table names etc..) Access renamed the tables (remember about
26 in each db) from one of the orders appending a "1" after the name to
distinguish the tables from either database - Not sure if that would be a
problem when trying to set up a master database that gets the data from all
orders and new orders that will be continuously coming in over time.
Thanks again.

On Thu, 6 Jan 2005 06:47:06 -0800, "Colin"
wrote:

Thank you for your help. I will try and get something up and running using a
master database. I believe some issues may arrise with the file size of the
master as each order's data is added (manually or otherwise). I tried the Get
External Data deal before, just trying to link 2 orders, bringing one into
another, and noticed that as all of the order databases are set up the same
(e.g.: the same table names etc..) Access renamed the tables (remember about
26 in each db) from one of the orders appending a "1" after the name to
distinguish the tables from either database - Not sure if that would be a
problem when trying to set up a master database that gets the data from all
orders and new orders that will be continuously coming in over time.

The aliases shouldn't be a problem; if you just use the Linked Table
Manager (or code) to relink to each new order database in turn, then
OrderDetail1 will just be the name of whichever order detail table
you're currently working with.

Size might indeed be a problem; I didn't realize there were as many as
26 tables. A single Access .mdb file is limited to 2 GByte (2048
MByte) and in practice you should get a bit nervous over 1.5 gig.
Regular Compact and Repair will be essential; you also will want to be
selective about what you import. If some of these tables contain the
same data from order to order (lookup tables) then they would need to
be imported only once, for example; and you needn't import information
which will not be needed in your summaries and reports.

John W. Vinson[MVP]

The aliases shouldn't be a problem; if you just use the Linked Table
Manager (or code) to relink to each new order database in turn, then
OrderDetail1 will just be the name of whichever order detail table
you're currently working with.

Size might indeed be a problem; I didn't realize there were as many as
26 tables. A single Access .mdb file is limited to 2 GByte (2048
MByte) and in practice you should get a bit nervous over 1.5 gig.
Regular Compact and Repair will be essential; you also will want to be
selective about what you import. If some of these tables contain the
same data from order to order (lookup tables) then they would need to
be imported only once, for example; and you needn't import information
which will not be needed in your summaries and reports.

John W. Vinson[MVP]
**************************
Thanks again. I'll give it a whirl and keep my fingers crossed.