Split an Access database to increase application performance

by Mary Ann Richardson | Feb 06, 2007 8:00:00 AM

Tags: File servers, Microsoft Office, Databases, Mary Ann Richardson, Microsoft Access, database, application code, file server, Access Application, Microsoft Office Suite Access Tips Newsletter

Takeaway: Trim the fat and make your Access app more nimble! By splitting the database, you can keep the bulk of the memory-heavy application on the network file server, leaving the process you really need on each workstation. Mary Ann Richardson shows you how.

Access works better if the application code is stored locally and the data is stored on the network file server; Access only needs to send data over the network, not the entire application.

One of the first steps toward making an Access application ready for the enterprise is to split the application code from the actual data—that is, place the data tables on the network file server and leave a copy of the application code on each workstation. When a user splits an Access application, tables containing the data are in one MDB file, and the rest of the database objects (which make up the application) are in another MDB file. When a user makes changes to the application code, the changes upload to the file server and then download to each workstation—there is no contact with the data tables during the change process.

To split a database, follow these steps:

1. Open the database you want to split.
2. Go to Tool | Database Utilities | Database Splitter.
3. Navigate to the folder where you want the database data to reside.
4. Click the Split button.
5. Click OK when the process is complete.

When users open the Access application at their workstation, they will see a special icon indicating that the tables link to the actual table on the file server.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Similar to adp file?huixtepecano@...  | 02/06/07

Are you sure ?kaspencer  | 02/07/07

Benefits, yes, but at a costIan Thurston  | 02/07/07

Could Not say it in a better way...manpapad@...  | 02/07/07

Of course ...kaspencer  | 02/07/07

Is this a bad joke?Tony Hopkinson  | 02/07/07

Bigger is seldom better for my clientsIan Thurston  | 02/07/07

Well you said it yourselfTony Hopkinson  | 02/07/07

Keep App split but also on servertlabrum@...  | 02/07/07

Not Quite...manpapad@...  | 02/07/07

'Enterprise'talentonloan  | 02/07/07

Well said....victor.gutzler@...  | 02/07/07

Small database design - big database usemslizny  | 02/07/07

Indeed it does and accessTony Hopkinson  | 02/07/07

Thats one way to do itjsargent@...  | 02/07/07

Other reasons to splitcmcfarla  | 02/08/07

The article is right...manpapad@...  | 02/12/07

multiple users will thank you tooTheComputerator  | 02/12/07

Splitting the databasegirish@...  | 02/22/07

splitting the databaseTheComputerator  | 02/22/07

Possible Solutionakapaul2002@...  | 04/21/08

Question about splitting the DBwsalamo  | 12/06/07

Back End/Front End and Duct Tapeakapaul2002@...  | 04/21/08