Use Wizard to Link Microsoft Access to MS Access Data (Part 4 of 4)
June 27, 2009 | 7:56 pmIf you’re new here, you may want to subscribe to my RSS feed. Thanks for visiting!
Use Wizard to Link Microsoft Access to MS Access Data
(Part 4 of 4)
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.
- On your computer, make a copy of the database that you want to split and place it on your local hard drive.
- Open the copy that is on your local hard disk drive.
- On the Database Tools tab, in the Move Data group, choose Access Database for the Database Splitter Wizard to start.
- In the Create Back-end Database dialog box, give the back-end a name. I always place the word ‘data’ in the name.
- 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:
- Open the database to be
split on a local drive. - From the main menu, choose
Tools then Database Utilities (Ad-Ins for Access 95 or 97) then
Database
Splitter. - 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’. - Browse to the server and
folder where the back-end file will be stored. - Now click on the Split
button. - A message will appear when
the split is complete. - Test the front-end
database to confirm that the proper links have been created. - 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 the 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
http://www.mvps.org/access/tables/tbl0012.htm