Home	Excel	Access
| Links | Books | Alphabetical Index | Access database queries |

Linking Excel to an Access table

The examples on this page were created using Excel and Access 2003.

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 at the click of a button.

1. Select menu option: Data | Import External Data | Import Data... |

Would you like to learn more?



Excel 2003 Formulas by John Walkenbach (with CD)

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 added to each row in an adjacent column to the linked data, this can be automatically copied down all of the matching rows. 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.

A link can alternatively be achieved using an SQL query. If you don't know how to write an SQL query, let Access do it for you. Write a select query in Access and then change from Design or Worksheet mode into SQL mode. Copy the resulting text and paste it into the Excel OLE DB Query dialog box command text field.

The possible advantage of using an SQL declaration is that it can be edited within the spreadsheet itself. In the example above it would be simpler to locally edit the start date within the command text than opening the Access database and modifying the query design.

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.

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

Click here for details about obtaining this file