Working with multiple Databases 
Author Message
 Working with multiple Databases

Is there an easy way to combine all records from several databases
into one master database?

I have an application that stores each day's data in a separate MDB at
midnight.  I need to be able to enter a range of dates and combine the
corresponding databases into one database to use for reporting.

Thanks



Tue, 01 Feb 2000 03:00:00 GMT  
 Working with multiple Databases

Quote:
>Subject: Working with multiple Databases

>Date: Fri, 15 Aug 1997 21:04:53 GMT

>Is there an easy way to combine all records from several databases
>into one master database?

>I have an application that stores each day's data in a separate MDB at
>midnight.  I need to be able to enter a range of dates and combine the
>corresponding databases into one database to use for reporting.

>Thanks

Rob,

Here is an outline what you need to do:
1Create a Master.mdb to be the top level database
2 Create a table to hold you database names ( in Master.mdb)

When you create a database store its name in the <DatabasesTable> ( If the
format of the databse stays the same n have a copy of an empty  database
lets call it "dbcopy.mdb" and rename it to the name you want ( FileCopy
<File1> <File2>

Now reporting:

2) Read the <DatabaseTable> from the Master.Mdb and process each of the
databases that meet you criteria.

You can create tables on the fly but watch for multi user conflicts.

The best way is to create needed tables in MS Access and dump the
processed data from the rest of databases/.

Hope this helps
George



Wed, 02 Feb 2000 03:00:00 GMT  
 Working with multiple Databases



Quote:
> Is there an easy way to combine all records from several databases
> into one master database?

> I have an application that stores each day's data in a separate MDB at
> midnight.  I need to be able to enter a range of dates and combine the
> corresponding databases into one database to use for reporting.

This is most easily accomplished by, within one database, attaching the
tables that you need to access in the other databases.  Once you've done
this, the attached tables look like tables in your main database; the only
significant difference between an attached table and a regular table is
that you can't modify the structure of an attached table.  (Oh, and if you
move the database that contains the real table, you'll get an error any
time you try to do something with the attached table.)

Once you've set up the attachments, you can use regular append queries to
copy records into the master table.  

This assumes that have Access.  If you don't, you can still set up attached
tables by manipulating the properties of the DAO objects.  You need to
create new TableDef objects within the database and set their Connect
properties in order to create the attachment.  Not simple, but not very
daunting either.

Bob Rossney



Wed, 02 Feb 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. HELP! Multiple Databases/Multiple Servers Access

2. Linking to multiple tables in multiple databases

3. multiple DoCmds not working

4. multiple addresses but only one works

5. Changing Categories for Multiple contacts not working

6. FileOpen With Multiple Maps not working...

7. multiple file macro works once then fails

8. Multiple Timers, Events Queue, DoEvents Don't Work

9. document.all method works only on multiple objects ?

10. Selection Formula for Multiple records won't work

11. multiple client programs working on a single list

12. Working with multiple CD drives using MCI API

 

 
Powered by phpBB® Forum Software