multiple sites

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

I'll be (trying) to set up a database (many, many tables it looks like)
where several sites will be responsible for entering and modifying data on a
local laptop, yet we want one "central" database to run aggregrate reports
from, etc. IOW, when and as the site data is gathered, transfer it to a
master database. These are class and demographic records, so thing like
attendance and such would be recorded regularly and at each site (about 10
sites).

Naturally, want to avoid problems with duplicate data, etc.

What's the best way to set such a beast up?

Thanks.
 
Bill said:
I'll be (trying) to set up a database (many, many tables it looks like)
where several sites will be responsible for entering and modifying data on a
local laptop, yet we want one "central" database to run aggregrate reports
from, etc. IOW, when and as the site data is gathered, transfer it to a
master database. These are class and demographic records, so thing like
attendance and such would be recorded regularly and at each site (about 10
sites).
Click to expand...


The Access feature for this kind of thing is called
Replication. You can probably find lots of info in Help,
the KB and on the web. But that's more complicated than I
want to deal with in a situation where all the sites do not
have to keep up to date copies of everything.

If the tables have primary keys that might not be unique
across all the sites (e.g. AutoNumber), the way I do it is
to have a field with the site ID. Then use a composite
primary key of both fields. This way you can merge data
from all the sites to a central site's table by just using
an Append query. If the sites keep their older data so each
of your merges would pick up the same old records along with
some new ones, then it's just a matter of adding a where
clause to exclude the records that are already in the master
table.

This situation gets a lot messier if the sites are allowed
to modify old records after they've been copied to the
master and Replication looks more attractive.
 
so under this plan, the sites would input data once, append to master, then
remove the "old" records from the sites (or us the where clause) and start
over with the next batch of new records?

Since there will be classes with attendance offered at these sites, I'm not
sure just how that would change things. The same person might attend more
than one class, and over much time so that we'd want to keep their profile
in the site database, and then update attendance for each class as they
attend (or not attend).


--Bill
 
so, been doing some simple tests with a replication database.

so far (and this is only simple tests) seems ok.

question: I'm using the access toolbar (tools, replication, etc.) to
manually sync. How do I create a button on a form to do this?

And, how do I have it try to sync with all 8 databases even if all 8 are not
available at the time? I expect only one at a time to be available (using
memory sticks), but want to keep it simple for the user.

Ideas?

Thanks.

Bill
 
Bill said:
so, been doing some simple tests with a replication database.

so far (and this is only simple tests) seems ok.

question: I'm using the access toolbar (tools, replication, etc.) to
manually sync. How do I create a button on a form to do this?

And, how do I have it try to sync with all 8 databases even if all 8 are not
available at the time? I expect only one at a time to be available (using
memory sticks), but want to keep it simple for the user.
Click to expand...


Like I said, I have managed to avoid using Replication so I
am not qualified to answer those questions. Try posting
them to the replication newsgroup.
 
Back
Top