I have a MS access Calibration database this is to be used in several countries.
We have a server but traffic needs to be kept at a minimum. (poor server)
The data on each user site is specific to each country, But (there’s always a but) Corporate would like to view each country and run stats on the combined data.
As more branches open they will need to taking on this database, extendibility is essential
My thinking is to have an individual data table sets held locally and periodically sent append data to a table on the server for corporate to view with there own bespoke front end.
Is this possible and how?
Or am I barking up the proverbial tree

You can do this as long as there are not conflicts in the data when it gets downstream (like PK field issues)

I would automate an update from the user's computer during downtime if that is possible. You can do this from the application, or from a script.

Also, what is the number of transactions that prevent you from making these updates one row at a time from the user's end when an insert or update is committed?

What kind of network are you on as well? Do the user's have to log in to the central network? Or is the connection persistent?



mmcdonal

The data is independent each country has its own records

Automating an update is the bit I don’t know
I have only done stand-alone applications before
plus this needs to be done by user

The server and other users are logged off at work end and being in different counties their times are different. Also IT are jittery at running applications over the network.

We have a secure network with logins for each user

This is problematic. You might have the users click a button to pass new data. You might automate it on open, since users are more likely to wait while the database is opening than when it is closing or some other time.

I would create a local log table (hidden) to log the data that was passed out, and check the table to see what new files need to be passed.

You can do this by either creating a DateCreated and DateModified field in all of the tables that will pass data, and then check the date and time of the last update, and then send records where either the date created or date modified is later, Or create a Yes/No field, and then check for yes if the data is new or updated, and then UNCHECK the field when the record is sent.

I would be inclined to do all this on the On Open event of the main form that opens when the users open the application. BUT it depends on how much data is going to be moved around.

Also, with error checking, you will want to have some method to confirm that the data that was sent was committed on the main database. And perhaps a check to make sure no errors were thrown during transmission, etc. Then perhaps some back checking from the main database...

How much data are we talking about on a daily basis from the busiest site?

Do you need more help on this?




mmcdonal

Thanks for youre help
on last question, To send this data should I use an update query?