Linking Excel to an Access table

It is quite simple to link an Access database table or a select type query to an Excel worksheet. Once the link has been defined, the data is saved on the worksheet and can be refreshed periodically or on demand.

Connecting to a Table or Select Query

1. Excel 2010 select ribbon option [Data] Get external data | from Access |

   Excel 2003 select menu option: Data | Import External Data | Import Data... |

2. In the Select Data Source dialog box, search through the folders on your computer and find the database containing the required data.
3. When you click to Open, another dialog box lets you Select Table. Choose a data source from the list of select queries and data tables.
4. An Import Data dialog box opens and allows you to choose the worksheet cell for the top left hand corner of the data table - or you can choose to place it on a new worksheet.

If a function is required in each row in an adjacent column to the linked data, this can be automatically copied down all of the matching rows. Type a formula in an adjacent cell and Excel will use it to generate a new column. If the main table data is refreshed and lines or added or removed, the formula column will be similarly adjusted.

To do the same thing in Excel 2003 - select a cell within the range of the data table and then open the menu option: Data | Import External Data | Data Range Properties | or alternatively right mouse button click and select Data Range Properties from the short context menu. The final option in the dialog box allows you to 'Fill Down formulas in columns adjacent to data'.

The data can be updated (to match the source database) at any time using the right mouse button click and select 'Refresh' from the short context menu.

Creating your own query to read database data

The Excel ribbon button for connecting to Access is intended for existing tables and select queries. A different method can be used to connect and directly extract information from a database.

	Select [Data] Get external data | From other sources | From Microsoft query |. The Microsoft Query program is separate to Excel, but is tightly integrated and sets up the live connection between Access and Excel. In the Choose Data Source dialog box, select the MS Access Database option. In the Open file dialog box, find the appropriate database.
	The Query Wizard then starts and prompts you to choose a data object and then select the required fields. It is possible to select fields from more than one table or query. This will require you to drop out of the wizard and complete your task using the Query program editor. Although the Access object may itself contain criteria to exclude unwanted records, the Query Wizard offers you the option of adding additional filters before importing the data.
	Another dialog lets you choose a field on which to sort the data. Finally the wizard lets you either make further changes to your selection using the Query editor, or to save the query and return data to Microsoft Excel. In the Import Data dialog box, select a worksheet and the cell in which to place the top left hand corner of the imported records. Generally it is much simpler to create the select query within Access and link to it rather than use the Microsoft Query program.

The option [Data] Connections | Connections | Properties | contains the settings for refreshing the data - either upon opening the spreadsheet or after a set time interval.

Note that while an Excel spreadsheet containing a linked table is open, the source Access database can only be opened in 'read-only' mode. It is effectively locked cannot be edited.

Once a data table has been created on a worksheet, it can be interrogated and summarised on another worksheet using functions such as SUMPRODUCT(), MATCH() or VLOOKUP(). If a list of account codes is linked and imported, it could be used to verify the input of codes on another form.

It is also possible to instruct Access to pass query results to a specific location within an Excel file.

The information on this page is included in the Excel functions guide.

Click here for details about obtaining this file. It has been rewritten for Excel 2010.