Quote Originally Posted by ArviLaanemets View Post
A scheduled task can be run on personal computer/laptop also. The only caveat is, it must working at time the task must run.


Doesn't look very effective way for this!
1. It looks the updating was done from same FE where all users worked (or BE was used, but all data was locked). The best way would be another FE (or even separate DB with it's own FE and BE) designed specially for this;


2. Probably the updating procedure did all the reading data, calculations, and writing data to table(s) as a single operation. When updating the table in this way takes too much time, then instead of this the procedure must read data, make all needed calculations, and save gotten data in separate table(s) (e.g. in updating FE or DB). When the procedure starts, as 1 step it clears all data from those temporary tables (for case something went wrong with previous update), processes rest of data reading, and writes data into temporary table(s). After that it updates real table(s) - either by insert/delete/update queries, or by clearing all data from original table(s) and inserting it anew (usually fastest way, but can be used only, when no autonumeric keys are used). Locking original tables is needed only for this part of procedure. And as last step of procedure, it clears temporary tables again - until next update, those temporary table(s) remain always empty.


Again, using updating DB (keeping all this in single FE can you easily reach DB size limits).
If there is a possibility the same info is read from clients DB repeatedly, then the updating DB must contain a table, where all PK's of all tables you read in from client DB's are registered along with matching PK values in main DB (e.g. tblKeys). This allows either ignore those entries in case the client's PK is registered, or to force the existing data in main DB to be updated (the choice is yours);
(on fly)
The temporary tables must contain a field for matching PK of main DB, which will ignored at 1st step of update. And probably also a similar field for every FK too (maybe you can cope without it, but it makes the converting of data more streamlined);
As 1st, the tables without any FK's must be updated. After that, the rest of tables in order, which ensures that the PK the FK is linked to was read previously;
The updating of main DB must be done table-wise, starting with temporary tables without FK's;
From operated temporary table, all records (not having the value of it's PK registered in tblKeys) are inserted into main DB table. And in case tblKeys is used, new values for client table PK's together with matching main table PK values are registered in tblKeys. After that, the matching temporary table is updated - the field for main PK is filled from main table. After that, the all FK's linked to this PK in other temporary tables are updated (or the field for main FK is filled);
And so on! When ordering of updating is done properly, for temporary tables having FK's there the matching main PK is present - so you can enter the new data into main table with proper values for FK's.
Wow! That definitely sounds beyond my skill level.

But to clarify, when that shipboard payroll system was initializing there was a status bar and a message that said "Please wait...populating tables". This usually took about 1-2 minutes to load (it was not instantaneous). They could not have been downloading everything on a satellite connection. I know there was a lot of data because an end of the month payroll period close could take a good 30-40 minutes to process. I assume something must have been mirrored on on our ships server. The Office ran Oracle as their DB server and we ran several in-house Access based apps. (Payroll, medical, daily reporting...) on the ship. All of this was custom developed in-house by our corporate developer. However he did it we were still able to keep working even when internet service was Marginal, so it was not reliant on an internet connection in order to function.