Understanding Access 2013 Databases

Linking Access to external data

Access has a great set of features for linking to data that is held in other systems. When a database is linked to data, if the data is then changed outside of the database for another system and the linked table is closed and then re-opened or refreshed by pressing Shift+F9, the changes in the data will appear. Earlier in this chapter, you learned that importing data makes a copy of the data. With linking, you dynamically view the data in another system or in an external file.

A link to data in another system will appear in the Navigation pane with a different icon than the standard local Access Table Icon. When you open a linked table in a number of situations, (depending on the type of data you are linked to), you can directly edit the data in the linked table. This changes the data stored in the originating file or system.

In organizations where data is held in IBM mainframes, on a Microsoft SQL Server server, on Excel spreadsheets, on Microsoft SharePoint, and in other sources, Access is a very powerful tool for both importing and linking to data. You can also use Access to produce management reports for data held in other systems.

In this exercise, you’ll link to a table of data in another Access database.

1. Click the External Data tab.

2. Click Access in the Import & Export group.

3. Locate the MSOfficeProData.accdb database file in the File Name box.

4. Change the default selection to Link to the data source and click OK.



Figure 37.38

5. Select the Products table, and click OK.



Figure 37.39



Figure 37.40

Notice that the icon for the linked table to products is slightly different than the one for a table held inside your Access database. If you open this table, you can edit, delete, insert, and update the data, and the data will change in the linked database. This is a very clever feature that allows Access to update the original data. Also in this example, the linked table name is shown as Products1, because you already have a table called Products.

Linking to data in Excel

Linking Access together with Excel means that you can share data between Office applications and take advantage of the strengths of each product. For example, you could have data in Excel, which you need to combine with data in Access, and rather than repeatedly importing a copy of the data from Excel, linking means that Access can work with a dynamic link to the Excel data. Therefore, when the data in Excel is updated, Access will always have the latest changes in the data.

In this exercise, you’ll link data in Excel.

1. Click the External Data tab.

2. Click Excel in the Import & Export group.

3. Locate the CompanyNames.xlsx file in the File Name dialog box.

4. Change the default selection to Link to the data source and click OK.



Figure 37.41

This will then launch the Link Spreadsheet Wizard.

5. Click First row contains column headers. Then click Next.

6. Because the sample database has already imported this table, change the default linked Table Name to CompaniesLinked. Click Finish.

7. Click OK. The table has now been linked.



Figure 37.42

A very subtle difference between this and a linked Access table in which you can change the data, is that with a linked Excel table, you cannot change the data. So, what you link to can determine the available features when you are working with the linked data.

TIP

After you link to tables from other sources, you may need to refresh the links to allow for changes in the design of tables in the other systems (for example, when using ODBC) or to allow for file paths that have changed. The Link Table Manager in the External Data Tab – Import & Link Group can guide you through the process of changing the links.