Manually Link Microsoft Access to MS Access Data (Part 3 of 4)June 25, 2009 | 7:56 pm
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:
- Copy the database.
- 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.
- 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.
- Rename to original file by adding the work data to it. This is your back-end file.
- Delete all the queries, forms, reports, macros and modules from the back-end file.
- 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.
- Compact both database files.
This first method will not lose any table relationships should you have any.
An alternative method is:
- Create a new empty database and name it with the word data in the name. This will be your back-end file.
- Import all of the tables into this back-end file.
- 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.
- Import all of the queries, forms, reports, macros and modules into the front-end file.
- 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/