Plug into your data: Connecting Excel to an Access database

Northwind Trading Company, a small wholesale food business, is doing quite well since they moved online. Retail merchants across the country buy everything  from salmon to granola and have it shipped quickly to their stores.

Their customer information is stored in an Access database, and now, the marketing team needs a better way to view it. In particular, they are interested in seeing where their customers reside so they can more effectively target their advertising dollars.

Microsoft Excel

Turn data into insights.

Create a data connection between Excel and Access

Luckily, the same features that Excel provides for viewing and organizing information in a spreadsheet, such as filtering, charting, and grouping, can be used to view and organize information in an Access database. But first you need to create the connection.

Note: We’ll use the sample Northwind Access database to demonstrate how Excel connects to data sources. You can download it here:  Northwind Web Database.

1. Go to the Data tab in Excel and click the From Access button.

2. On the Select Data Source dialog, go to the location where the Access database is stored, select it, and click the Open button

3. On the Select Table dialog, choose a table from the database to import.

4. Accept the default options on the Import Data dialog, and click OK.

Excel and Access are now connected, and the data from the Northwind CustomersExtended table appears in Excel.

Refresh data

Now that Northwind can now easily view and analyze the information in its Access database, they want to make sure they are reviewing the latest information.There’s a couple ways they can refresh the data in their workbook.

Forced Refresh

To force a refresh, click the Refresh All button on the Data tab. This will instantly import the latest Access data into Excel.

Customized Refresh

Northwind can customize the refresh behavior for their workbook in the following ways:

• Enable background refresh (this option allows them to continue working in Excel while the refresh operation executes).
• Refresh data after a specified time period (e.g. every 30 minutes)
• Refresh data when opening a workbook

1. Click the Connections button on the Data tab, and then click the Properties button on the Workbook Connections dialog.

2. On the Connection Properties dialog, select the desired Refresh control options and click OK.

All connected!

Northwind can now make better advertising decisions. Currently, they don’t have a single order from New York for their very popular gnocchi, and so they decide to focus their advertising on the the tratorrias of Little Italy.