Linking vs. Replicating vs. Splitting

[dunc723]

What are the advantages and disadvantages of linking tables in separate databases vs. replicating a database vs. splitting a database? I will try to describe a couple different applications:

1. Intermittent, short-term use - An external process opens Access to get a new sequential document number from a table; a form is opened to collect about 4 fields of information for that document number, it's stored in the table, and Access is closed. About 30 seconds of use. It's theoretically possible but statistically unlikely for more than one user to need the database at the same time. There could be up to 10 possible users. The database might have two or three simple tables and a form for each.

2. Longer term use - A user may open the database for up to a couple of hours to enter and update data and run reports. It's likely more than one user will use the database at the same time, but there are only three or four users. The database has multiple tables, queries, forms, and reports.

In both cases I have users at multiple remote sites around the world, and data connections can be very slow between sites. They must be able to open the database locally. I have used replication for the second application, but it can take 10 minutes or more and it's a manual step that each user must remember before and after changing any data. I tried splitting the database (putting the back end on a remote server) and opening the main data entry form or running a query seemed just as slow as opening the entire database from the remote server.

Linking seems like a good solution for the first application, but I don't have a feel for how fast the table will update between servers, or even when it updates (when the database opens? when the table opens?).

Thoughts and suggestions?

Thanks!

[ranman256]

If > 1 need the app. I would SPLIT. (the splitter does it for you)
Put the DB tbls on the server and give each user a copy of the APP.
Each app points to the db. All can use and update 1 set of data.


Now, if your going international, you may have separate DBs for the distance, so look into replication to unite the disparate DBs.