Daily Access Tips

I am showing the manual methods first before the automated “Database Splitter” wizard method because it offers several advantages over the split MS Access Database wizard to Link Microsoft Access front-end to MS Access back-end.

The advantages of the manual approach are:

• It will result in smaller sizes for both front-end and back-end databases.
• It will not leave your back-end unsecured. A secure database split with the wizard will leave the back-end database file unsecured.
• Table relationships will not be lost.
  

If you want to link Microsoft Access front-end to MS Access back-end there are two processes you can use. You can use either of these methods on any version of Access program.

Here is the first manual method:

1. Copy the database.
2. Give it a name similar name but add the word program or ‘prg’ to the name. This will be your front-end database program file.
3. Delete the tables from this front-end. Do not delete any “lookup tables”, if their data is not going to change. Example of a good local table: A list of State abbreviations.
4. Rename to original file by adding the work data to it. This is your back-end file.
5. Delete all the queries, forms, reports, macros and modules from the back-end file.
6. In the front-end, use the Linked Table Manager to link to all the tables in the back-end or to get your MS Access link you may manually link to each table.
7. Compact both database files.

This first method will not lose any table relationships should you have any.

An alternative method is:

1. Create a new empty database and name it with the word data in the name. This will be your back-end file.
2. Import all of the tables into this back-end file.
3. Create a second new empty database and name it with the word program or ‘prg’ in the name. This will be your front-end file.
4. Import all of the queries, forms, reports, macros and modules into the front-end file.
5. Use the Linked Tables manager to link to all the tables in the back-end file or manually link to each table to get your MS Access link.
   

There is a Microsoft KB Knowledge Base Article on how to do this at: http://support.microsoft.com/kb/304932

The Microsoft Developer Network MSDN also has an article on this subject: http://msdn.microsoft.com/en-us/library/aa141493(office.10).aspx

As a side note, Microsoft has always allowed an upgraded newer version of your front-end to Link Microsoft Access to MS Access Data back-end that are in earlier versions. For example, you might upgrade your Access 2002 version to Access 2007 for some of your users getting a new computer, however you will not have to upgrade the back-end data tables to 2007.

Continue to the next article in this series: http://www.dailyaccesstips.com/use-wizard-to-link-microsoft-access-to-ms-access-data-part-4-of-4/