networking - MS Access databases on slow network: Is it faster to separate back ends? -

i have access database containing information people (employee profiles , related information). front end has single console-like interface modifies 1 type of data @ time (such academic degrees 1 form, contact information another). linked multiple ends (one each type of data, , 1 basic profile information). files located on network share , many of ends encrypted.

the reason have done understand ms access has pull entire database file local computer in order make queries or updates, put changed data on network share. theory if person changing telephone number or address (contact information), have pull/modify/replace contact information database, rather pull single large database containing contact information, projects, degrees, awards, etc. change 1 telephone number, reducing potential locked databases , network traffic when multiple users accessing data.

is sane conclusion? misunderstand great deal? missing else?

i realize there consideration of overhead each file, don't know how great impact is. if consolidate ends, there potential benefit of being able let access handle referential integrity cascading deletes, etc., rather coding that...

i'd appreciate thoughts or (reasonably valid) criticisms.

this common misunderstanding:

ms access has pull entire database file local computer in order make queries or updates

consider query:

select first_name, last_name employees empid = 27; 

if empid indexed, database engine read enough of index find table rows match, read matching rows. if index includes unique constraint (say empid primary key), reading faster. database engine doesn't read entire table, nor entire index.

without index on empid, engine full table scan of employees table --- meaning have read every row table determine include matching empid values.

but either way, engine doesn't need read entire database ... clients, inventory, sales, etc. tables ... has no reason read data.

you're correct there overhead connections back-end database files. engine must manage lock file each database. don't know magnitude of impact. if me, create new back-end database , import tables others. make copy of front-end , re-link back-end tables. give opportunity examine performance impact directly.

seems me relational integrity should strong argument consolidating tables single back-end.

regarding locking, shouldn't ever need lock entire back-end database routine dml (insert, update, delete) operations. database base engine supports more granular locking. pessimistic vs. opportunistic locking --- whether lock occurs once begin editing row or deferred until save changed row.

actually "slow network" biggest concern if slow means wireless network. access safe on hard-wired lan.

edit: access not appropriate wan network environment. see this page albert d. kallal.