Splitting your Microsoft Access MDB into a front end and back end - Overview

[ Main | AccessSplitting ]

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.

Now put a copy of your app MDB/MDE on the client PCs. You can use the free Auto FE Updater utility to assist with this task.

Microsoft pages

There are no official Microsoft documents recommending you should split. 

Microsoft Office 2000/Visual Basic Programmer's Guide The Two-Database Approach
Microsoft Office Developer Forum - Building Applications with Access 97 - Delivering your Application - Separating your Application's Tables from Its Other Objects
Separating Your Applications Tables from Its Other Objects

Q304932 - ACC2002: How to Manually Split a Microsoft Access Database (Although these instructions apply to all versions of Access.)
Q296179 - ACC2000: How to Share a Database in a Dual-Version Environment
Q162522 - ADT/ODE: Distributing a Split Database Application with ODE/ADT

 

(Updated 2010-09-27)

[ Splitting | AccessMain ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright © 1995-2013 Tony Toews