Splitting a Database to Improve Efficiency

Standard Microsoft Access database interfaces are originally designed to be all encompassing within one file.  This file holds all the tables (data), queries, forms, reports, modules and macros.  This model works very well for small databases that are only going to be used by one person.  However, when the amount of data grows too much, or when it becomes necessary for multiple users to be utilizing the data simultaneously, it is a generally accepted practice to split a database into two files, the front-end (or interface) and the back-end (or data file).

When an Access database is split in two, all of the tables from the original file are exported into a new file.  Standard naming conventions suggest that the new file be named the same as the original file with an added “_be” before the file-type.  For example, if we were working with the database Sample.accdb, we would export the tables into a new file Sample_be.accdb.  The “_be” designates it as a back-end or data file for easy reference later.

This process, in essence, takes all of the data out of the original file and puts it in the new file.  In order to make the original file continue to work properly, it must be linked to the new back-end.  Linking to a back-end file involves creating a connection in the front-end to the back-end that will synchronize all data between the two files.

Microsoft Access contains a built in tool that will accomplish both of these tasks.  In the 2007+ version of Access, the tool is located in the Database Tools ribbon, in the Move Data section, and is labeled as Access Database.  This tool will ask for a new file name and will then proceed to export the tables to the new file and link to those new tables after they have been created.

The disadvantage of this tool is that it will export all the tables within the Access database.  There are times when some tables should not be exported.  For instance, if you have a table that includes information about the interface file, such as when it was last modified and by whom, this may be better kept separate in case there are multiple editions that are created later on.  The process can be done without the automated tool and it only takes slightly longer to do.

The first step of splitting a database manually is to create the new database where the tables will be exported to.  Make sure that you follow the proper naming conventions when creating the new data file, as explained above.

The second step is to export the tables to a new file.  This can be accomplished by selecting all of the tables that you wish to export in the Navigation menu on the left, and then selecting the export tool either by right clicking on one of the tables and selecting Export->Access, or under the External Data ribbon, in the Export section, choosing the Access option.  Here, you will be asked for the data file that you created in Step 1. Once you click Ok, you will be asked whether you want to export the structure and the data or just the structure.  In this case, you want to export the structure and the data.

The third and final step is to create the new links.  In the External Data ribbon, in the Import & Link section, select Access.  In the File name box, select the file that you created in Step 1.  In the options below it, select the second option entitled “Link to the data source by creating a linked table.”  In the popup, select all the tables you want to link to (you will need to select each one individually; using the shift key will not work) or use the Select All button to select all the tables in the file.  You will likely want to select all at this point.  Hit ok once the appropriate tables are selected and the link will be created.

The only disadvantage of this process is that it creates two files that must be moved together if the database is moved from one computer to the other.  Also, since the links are based on the location of the data file, if the data file is moved into another folder, or if it is put in a different location on a new computer, the links will need to be updated to account for the new location.  That, however, will be the next topic of this blog series, so you will need to come back to find out more!

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *