A best practice in designing any Access database is to split the database into a front-end containing Access forms and code and a back-end containing just tables and indexes, either as an Access MDB or ACCDB or preferrably a SQL Server database.
Even with this setup you can still run into corruption problems when the front-end is shared between multiple users. Access has a built in utility - Compact and Repair which will clean up temporary objects, cached query results etc. When allowed to run once a day this will typically keep the system running well. However as soon as multiple users are involved it is likely that one or more users will leave the database open for days on end. That means Compact and Repair will never run and eventually you will end up with corruption.
One way to avoid this problem is to use a startup launcher, commercial packages are available (listed below) but a simple batch file can be used - give each user a shortcut to the launcher script, this in turn makes a copy of the front end for each user and then opens the database. When that user closes their copy of the database Compact and Repair will be able to run.
This technique can also be used to deliver new versions of the database, incrementing the version number will cause a fresh copy to be made the next time each user opens the launcher.
On a modern network this will add a small startup delay of a second or two, but only for the first run, normal use thereafter should have no noticable startup delay.
LAUNCH.CMD
@Echo Off Setlocal :: Access Launcher, this script will first copy an Access database to a local folder :: and then launch it. :: The source path of the database and version.txt Set _source=T:\databases :: The destination for the local front end copy (=%HOMEDRIVE%) Set _destination=C:\downloads :: The version of the database - increment when changes are made. Set _version=1.0 :: The source name of the database file without extension Set _source_db_name=demo :: The destination name of the database file without extension Set _dest_db_name=demo :: The file extension, typically MDE or ACCDE Set _source_extension=mde Set _dest_extension=mde :: Tip - To prevent anyone opening and working directly from the source file you can :: rename and use a source file extension like .AAA which will not open in Access. :: Read the destination version no. if exist %_destination%\version.txt (for /f "delims=*" %%G in (%_destination%\version.txt) do set _dest_ver=%%G) Else set _dest_ver=0 :: echo Dest is %_dest_ver% if %_version% EQU %_dest_ver% goto :launch :: New version found so copy database copy "%_source%\%_source_db_name%.%_source_extension%" "%_destination%\%_dest_db_name%.%_dest_extension%" >nul if %errorlevel% GEQ 1 goto :launch :: copy worked so now write version.txt echo %_version% >%_destination%\version.txt :launch :: Open database Start "Launching" "%_destination%\%_dest_db_name%.%_dest_extension%"
“Your job is your bosses, he can give it to someone else, your career is yours you can take it with you” ~ Jeffrey Snover
Related
AutoFEupdater - Automatically copy files from a server to a folder on each users PC
FMSinc startup - Control startup versions, bitness