If 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.

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 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

Spending too much time searching for and manually cut and pasting just to move data from one software to another?

Wish a one button push could automate many repetitive actions of moving data, running calculations and then moving results to another area?

You don’t need a big expensive custom business software package to help you with this when a training session and some tips will greatly reduce your daily workload from hours to minutes.

Tools and training via phone and screen sharing is available at a convenient time .

Free to super low cost help is available now,

Bob Heifler

I am here to help you at MyAccessProgram.com for one on one training and

tips to reduce your workload.

No cost consulting conversations are welcome.

Call 818-294-2827 today.

Contact …

Spending too much time searching for and manually cut and pasting

just to move data from one software area to another?

Find out how a short training session and some tips will greatly reduce your daily workload from hours to minutes.

Bob Heifler

A single one button push could automate many repetitive actions of moving data, running calculations and then moving results to another area?

MyAccessProgram.com for one on one training and tips to reduce your workload.

No cost consulting conversations are welcome.

Tools and training via phone and screen sharing is available.

Contact me…