The manual export of data from MS Access to MS Excel spreadsheet is useful for reviewing the query results from the data set that is static. It means that database does not need any additional data entry because the researcher is done with the data accumulation, so the results obtained from a database does not change. This implies that the copy and paste method is not efficient if there is a need for continual database update. Also, there is the issue that the people who need the query results are not familiar with the MS Access environment.

The dynamic linking of data from MS Access query to MS Excel worksheet solves the issue. An addition or any modification of data into the database is automatically reflected into the query results of the database. The link echoes the changes from the MS Access query to the MS Excel spreadsheet. The updates happen each time the MS Excel spreadsheet is opened or the Refresh Data is selected.

To have an overview of linking spreadsheets into a database, click here to watch an approximately 6.5 minute tutorial video. This is an 6.5MB flash video requiring Flash Player and speakers or headset.

Linking a spreadsheet dynamically to a database

1. Open a MS Excel file. Go to Data -> Import External Data -> New Database Query....

2. The Choose Data Source dialog appears, select MS Access Database and click OK.

3. Choose the database by browsing through its path. Tick the Read Only box and click OK.

• For this example, the Nhanes.mdb is the database we need to have a Read Only link in the spreadsheet.

4. In the Query Wizard, select the query that should be linked into the MS Excel spreadsheet and click ">" to select all the columns that are present in the query. Then click Next.

• In the example, summary_bmi is the query that needs to exported into the spreadsheet for review.

• All the columns from the summary_bmi query will be linked into the spreadsheet. It includes the ethn, sex, avg_bmi, min_bmi, max_bmi and cnt_bmi.

• For the Query Wizard - Filter Data and Sort Order, just click Next. This is to make sure that filtering and sorting of query results should be done in the MS Access query and not on the MS Excel spreadsheet.

5. Select Return Data to Microsoft Office Excel, and click Finish.

6. For Import Data dialog, select the Existing worksheet: and click OK.

7. The query results appear into the MS Excel spreadsheet. Save the spreadsheet.

8. To enable the update of the query result each time the spreadsheet is opened, go to Data -> Import External Data -> Data Range Properties.

9. In the Properties Window, tick the Refresh data on file open and Remove external data from worksheet before saving. Click OK. When done with the data review, Close the file.

• Refreshing data on file open enables the updating of the query result each time the file is opened.
• Removing external data from worksheet before saving assures that the previous output is not saved.

• Each time the file is opened, Query Refresh gives the option to enable or disable automatic refresh. Select Enable Automatic Refresh every time.

10. The manual data refresh can also be done to update the spreadsheet without closing the MS Excel file. This is useful if there are new records added in the database, or there is a change in the query's WHERE condition, that should be reflected in the spreadsheet.

• Just click on Data and then, select Refresh Data. The spreadsheet will then have an updated query result.

Comments

1. If you change the database query so that it returns a different number or order of columns, you have to clear the data range in the spreadsheet and go through the linking process again.

2. If you change the query so that it returns a different number or order of rows, you only need to refresh the data, either from the data menu option, or by closing and opening the spreadsheet.

3. If you add or remove data to the database and the same query results in a different number of rows, you only need to refresh the data. This is useful when data are frequently added to a database and the database queries perform complex merging, aggregation, filtering, and sorting of the data. Opening the spreadsheet triggers the queries to be executed on the updated data in the database, and the results are transferred automatically to the spreadsheet. This happens without the need to open the database.