Create Single Data Set from mulitple data sources

Create Single Data Set from mulitple data sources

Post by Shane G. Brodi » Thu, 06 Apr 2000 04:00:00



The scenario:

I have an application on two or more machines which have identical MS Access
2000 databases.  The records in each database are unique to the
machine(unique terminal number in each table key).  I need to report on a
union of all of the tables for all machines.  Is there a simple way of doing
this(create a union query on multiple data sources???).  Or must I create a
new reporting database and append all records to it and then report on it?
I need the data from all machines at once in order to create summary
information in my reports.

I've thought of replication, but am worried about application stability and
database bloat.  For some reason, setting up replication causes the
databases to grow expontentialy( a 958K database becomes a 10.3 MB
database!).

Anybody having any ideas or experience with a similar problem, I could
really use the benefit of your insight......

Regards

Shane G. Brodie

 
 
 

Create Single Data Set from mulitple data sources

Post by Kevin Bennet » Thu, 06 Apr 2000 04:00:00


What an interesting problem!

Try this

Create a disconnected recordset on each of the Access databases
Choose one of the disconnected recordsets as the "master"
Cycle through the records in the other recordsets, issuing the Addnew
command on the master recordset for every record you find.
You'll now have one recordset containing all of the records.

KB



Quote:> The scenario:

> I have an application on two or more machines which have identical MS
Access
> 2000 databases.  The records in each database are unique to the
> machine(unique terminal number in each table key).  I need to report on a
> union of all of the tables for all machines.  Is there a simple way of
doing
> this(create a union query on multiple data sources???).  Or must I create
a
> new reporting database and append all records to it and then report on it?
> I need the data from all machines at once in order to create summary
> information in my reports.

> I've thought of replication, but am worried about application stability
and
> database bloat.  For some reason, setting up replication causes the
> databases to grow expontentialy( a 958K database becomes a 10.3 MB
> database!).

> Anybody having any ideas or experience with a similar problem, I could
> really use the benefit of your insight......

> Regards

> Shane G. Brodie


 
 
 

Create Single Data Set from mulitple data sources

Post by Shane G. Brodi » Fri, 07 Apr 2000 04:00:00


Thanks for the input.  That is the direction I was leaning.

Regards

Shane Brodie


| What an interesting problem!
|
| Try this
|
| Create a disconnected recordset on each of the Access databases
| Choose one of the disconnected recordsets as the "master"
| Cycle through the records in the other recordsets, issuing the Addnew
| command on the master recordset for every record you find.
| You'll now have one recordset containing all of the records.
|
| KB
|
|


| > The scenario:
| >
| > I have an application on two or more machines which have identical MS
| Access
| > 2000 databases.  The records in each database are unique to the
| > machine(unique terminal number in each table key).  I need to report on
a
| > union of all of the tables for all machines.  Is there a simple way of
| doing
| > this(create a union query on multiple data sources???).  Or must I
create
| a
| > new reporting database and append all records to it and then report on
it?
| > I need the data from all machines at once in order to create summary
| > information in my reports.
| >
| > I've thought of replication, but am worried about application stability
| and
| > database bloat.  For some reason, setting up replication causes the
| > databases to grow expontentialy( a 958K database becomes a 10.3 MB
| > database!).
| >
| > Anybody having any ideas or experience with a similar problem, I could
| > really use the benefit of your insight......
| >
| >
| > Regards
| >
| > Shane G. Brodie
| >
| >
| >
|
|

 
 
 

1. Joining Data from mulitple Fields to a Single Field

Hello, I've looked though my books (including Scriptology) But can't seem to
find what I'm trying to do. I've got a database that has a serial number
entry form. The way our serial numbers work it has several parts (alpha and
numeric), that all have a meaning. For the most flexible and easiest
solutiion I have each part of the SN in its own field. But what I want to do
is have another field that can join each of these fields to a single SN that
matches what I have in the seperate fields. I know this has got to be a
simple solution but I can't for the life of me figure it out. Thanks in
advance.

Brian

2. ODBC w/I-NET yet?

3. setting properties on an OLE DB when creating data source

4. Print Dialog Box from ObjectPal

5. Create User Control as Data Source Using ADO Data Control

6. xp_sendmail with attachments.

7. Creating a control class that can be a data binding data source

8. Which database is better - updated request

9. Data Source: Programmatically Configuring an ODBC Data Source - HELP PLEASE

10. Help:ODBC Data Source Admin doesn't show Names of new Data Sources

11. cannot add MSAccess 97 data source in ODBC Data Source Admin

12. How to get a single recordset from two Data Sources using ADO

13. Business Objects: Single universe and multiple data sources ?