[ Main | Access | Splitting ]
|You want to split the MDB into a front end (FE) containing the queries,
forms, reports, macros and modules with just the tables and relationships in the
back end (BE) which resides on a server. From within the FE you link to
the tables in the BE. In Access 97, 2000 and 2002 that's File >> Get
External Data >> Link Tables. Any new fields, tables or relationships or
updates thereof get done in the BE.
You make updates to the FE MDB and distribute them to the users, likely as an MDE. There is a wizard in Access which can do the splitting.
If you have multiple versions of Access you must support then convert your FE to to the other version of Access. Make it an MDE. Then distribute it to the other versions users. Keep the backend in the lowest version, ie Access 97. (This doesn't apply to Access 2000 and Access 2002 as Access 2002 can execute Access 2000 MDEs.)
However the wizard doesn't copy the relationships window. Use the sysrels utility if this is important to you. There is no need to make the backend an MDE as it should only have tables and relationships in it anyhow. (Although we did add a small startup form which basically tells the users to get out as making changes at this level are dangerous.) Also don't put a database password on the MDB has this greatly complicates the repair if the backend should somehow get corrupted.
You then link the tables from the FE to the BE. There is sample code at the
Access Web's Relink
Access tables from code article which will refresh the links.
There are no official Microsoft documents recommending you should split.
Microsoft Office 2000/Visual Basic Programmer's Guide The Two-Database Approach
Q304932 - ACC2002: How to Manually Split a Microsoft Access Database
(Although these instructions apply to all versions of Access.)
[ Splitting | Access | Main ]
Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.
Granite Fleet Manager - the best designed fleet maintenance tracking and management system available