Linking Access to an Excel Worksheet

Excel is great for number crunching, and can even be used as a basic database to store information.  However, when it comes to displaying and presenting information, the MS Access database has some big advantages over it's peer in the Office 365 suite of software. The reason for this is that Access has a number of tools and features specifically designed to make data easier to read and understand - the Reports facility being just one.

The good news is that Access has the ability to connect to, and work with, Excel data in two different ways.  Firstly, Access can import data from an Excel worksheet into an Access table.  This method lets us work with a "snapshot" of data from Excel as it was at the time the data was extracted (there is more information about importing from Excel in my post on "Importing and Exporting Data between Access and Excel").  The second method is to set up a "live" link between the Excel worksheet and the Access table. This has the advantage of giving us direct access to current data in the spreadsheet; so any updates, additions or deletions will show up in Access (after data is refreshed/reloaded) as soon as the data is saved in Excel. However, please do note that this connection is read-only; new data cannot be saved back into the Excel spreadsheet without specifically doing an additional export.

Today we are going to learn how to set up this "live" link. To do so, I have set up a sample Excel worksheet with fictitious personnel records.

Linking to Excel

1. Create a new Access Database, or open an existing one.
2. Select the EXTERNAL DATA ribbon.
3. Click the NEW DATA SOURCE icon in the IMPORT AND LINK group.
   

   

   Above: Adding a New Data Source from the External Data ribbon.

4. Select FROM FILE from the drop-down menu, and then EXCEL from the sub-menu.  This opens the GET EXTERNAL DATA - EXCEL SPREADSHEET dialog form.
   

   

   Above: The Get External Data - Excel Spreadsheet dialog form.

5. Click the BROWSE button and select the Excel spreadsheet you want to link to.
6. Click the LINK TO A DATA SOURCE BY CREATING A LINKED TABLE radio button, and then click OK.  
7. The next page of the wizard opens where you should see a sample of the worksheet you are linking to.  If your worksheet had column headings, ensure the FIRST ROW CONTAINS COLUMN HEADINGS box is ticked, and click NEXT. (Please note that if your spreadsheet has more than one worksheet or named range, you will see an additional wizard page asking you to select the specific worksheet or range before the page in the screenshot below).
   

   

   Above: First page of the Link Spreadsheet Wizard.

8. Enter the name you are going to call the linked Access table in the last page of the wizard, and click FINISH.

Once you have done this, you will see the new linked table appear in the left-hand Access navigation pane under TABLES.  Note how the linked table icon is a blue arrow pointing to the Excel logo, thereby indicating the table is linked to an Excel spreadsheet.

Above: The table linked to Excel.

Now the link has been established to the Excel worksheet, we are free to base forms, reports and queries on the linked table as if it is native to Access itself.