Daily Access Tips

Each version of Microsoft Access has a built-in wizard to link Microsoft Access front-end file to MS Access back-end file in order to get your MS Access link between the two files.

You will find the wizard on the menu bar or ribbon at the top of the Access window. Here are the menu paths to get to your wizard.

Access 95-97 Tools |
Add-Ins | Database Splitter

Access 2000 – 2003 Tools |
Database Utilities | Database Splitter

Access 2007 Database Tools
| Move Data | Access Back-End

Here is how to use the Wizard in Access 2007:

Note: The wizard doesn’t copy the table relationships, if you have set any up.

1. On your computer, make a copy of the database that you want to split and place it on your local hard drive.
2. Open the copy that is on your local hard disk drive.
3. On the Database Tools tab, in the Move Data group, choose Access Database for the Database Splitter Wizard to start.
4. In the Create Back-end Database dialog box, give the back-end a name. I always place the word ‘data’ in the name.
5. To get your MS Access link for the correct back-end location, in the File Name box, you enter the path to the network in front of the file name. If the network location for the
   back-end database was \\server_fruit\share_folder_apple\ and the file name for the back-end database is MyDB_Data.accdb, you would enter \\server_fruit share_folder_apple\ MyDB_Data.accdb in the File Name box.

Note: It’s better to use the double slash UNC (UNC: Acronym for Universal Naming Convention or Uniform Naming Convention) rather than a mapped drive letter that might get unmapped.

After the database splitting procedure is finished, you can re-link the linked tables using Linked Table Manger to link Microsoft Access front-end file to MS Access back-end file.
Go to Database Tools from the Access Ribbon and select Linked Table Manager. In earlier Access versions, it’s under Tools, Database Utilities. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location.

In Access 95-2003 do the following:

1. Open the database to be split on a local drive.
2. From the main menu, choose Tools then Database Utilities (Ad-Ins for Access 95 or 97) then Database Splitter.
3. The default file name for the back-end will show as DatabaseName_be.mdb (for Access 2003). The letters ‘be’ stands for back-end. I choose to use the word ‘data’ instead of the letters ‘be’.
4. Browse to the server and folder where the back-end file will be stored.
5. Now click on the Split button.
6. A message will appear when the split is complete.
7. Test the front-end database to confirm that the proper links have been created.
8. Now that you have your MS Access link, distribute the front-end database to your users, so that they can start to use the database.

Access MVP, Microsoft Most Valuable Professional, Dev Ashish has got some code that looks during program start up at each table in the database, and tries to find the data source for that table if the Connect property is populated. If the database specified for the linked table is not present, the code brings up a dialog window so that the user can select an alternative source. The code will then proceed to link Microsoft Access front-end file to MS Access back-end file.

Find Dev Ashish’s code here: http://www.mvps.org/access/tables/tbl0009.htm

Dev Ashish also references some code, for when one Access database has linked tables from different data sources (ODBC, Excel, FoxPro etc), that can create your MS Access link to all linked sources, regardless of their source type.

Here’s the code: http://www.mvps.org/access/tables/tbl0012.htm

Well that wraps up this series of articles.  Your comments and acknowledgments are always welcome.

Bob Heifler