Have you ever wanted to use data from Excel in an Access database? Because Excel and Access both store data in tables in a similar way, each application can recognize data stored in the other application. As there are more Excel users than Access users there tends to be much more data stored in Excel compared to Access in some organisations, although much of this data could also be very useful in Access, with its more powerful capabilities to create queries, forms and reports. One way to use Excel data in Access is to link an Excel worksheet to an Access table. This article describes how to do this.

You can copy and paste or import an Excel table into Access to create a brand new table which has all the fields and data from Excel, but if the original Excel data changes, the Access data does not change. However if you link an Excel table to an Access table, your Access table again has all the fields and data from Excel, but if the original Excel data changes, the Access data also changes. The benefit in doing this is that you have all the power of Access to manipulate the data in the normal Access ways, but the original Excel data, which might be stored in a shared folder, is still available to other users. Data changes in the original Excel table will always change data in the Access linked table.

All versions of Access allow linking to Excel spreadsheets. Suppose you have an existing Excel spreadsheet called 'STAFF1' stored in a folder called 'TEST' on your desktop. Let's say this spreadsheet has a small table near the top of sheet1 with headings and three or four rows of data. All columns of data under each heading must be the same data type. You can also have Excel tables in named ranges within a workbook, but on this occasion the table is simply in sheet1. We'll go over how to create a linked table to this file in an Access database.

To create a linked table in an open Access 97-2003 database, choose 'File', 'Get External Data', 'Link Tables'. In the search window change the file type selector to Excel , then navigate to the Excel file STAFF1 and double click it. The Access Link Tables wizard launches.

To create a linked table in an open Access 2007/2010 database, select the 'External Data' tab, and in 'Import/ Link' group click the Excel button. In the wizard choose the 'Link' option, browse to find the Excel file STAFF1 and double click it. The Access 'Link Tables' wizard launches. By the way Access 2007/2010 will happily link to files from any Excel version.

The Access wizard will normally recognize the Excel data and preview the table in the first wizard screen. You can then click 'Next' several times and proceed to the last wizard screen and give the new linked table a name such as 'TblStaff', and click 'Finish' to complete. Then click OK in the resulting Access prompt which tells you the link is established.

In Access the linked table icon is slightly different to usual table icons, with a small arrow in the icon to indicate a linked table. You can work with the linked Access table in exactly the same way as a regular table, so you can create queries, forms and reports based on this table. If the data in the original Excel table is changed the table in the linked Access table is also changed. You can also change the data in the Access table because the link is two way. However you cannot change the table design as this is determined by Excel. If you change to table Design View Access prompts you that the Design View will open in Read Only mode, so you can view but not change the design.

When the link is created, the Excel file is linked to the Access database, so it's important that the Excel file is not renamed, moved or deleted. However if the file is renamed or moved Access has a link manager feature which allows you to re-link the table to the renamed or moved file.

So in conclusion, linking an Excel file to an Access database lets you use the more powerful analysis features in Access such as querying and form building without disturbing the original Excel file which users can continue to use. And because the link is made from Excel to Access, and changes in the Excel file will cause the same changes in the Access table. Once you've got the hang of this you might be interested in learning more about Access. A really effective way is to attend a training course and really see your Access skills take off.