Link an Excel database table to an Access database

by Mary Ann Richardson | Jun 30, 2006 7:00:00 AM

Tags: Microsoft Office, Databases, Mary Ann Richardson, Microsoft Access, Microsoft Excel, database, Microsoft Office Suite Access Tips Newsletter

Takeaway: Microsoft Office is more than the sum of its parts—you can link an Excel database table to an Access database, integrating your data and adding value. Here's how.

You don't have to import an Excel database table into a new Access table in order to work with it in an Access database. For example, suppose you track student grades in an Excel database and you would like to create an Access query that would list which students scored above 85% on all three exams. Follow these steps:

1. Open Access and create a new blank database.
2. Go to File | Get External Data | Link Tables.
3. Navigate to the Student Data File in your Excel folder.
4. Select the Worksheet.
5. Click Next twice.
6. Click Finished.

A link to the Excel database will appear in the Access database window under Tables. You can select the link and create a query as you normally would. When you link to an Excel table, the original Excel table will display all Access operations. You can create queries, forms, and reports, and add or delete data to the Excel table—just as you could do if you had imported it as an Access table. Later, you can use a Make Table query to create an Access table from the Excel data, if you so desire.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

How about vice versa?Tink!  | 07/20/06

Linking Excel to AccessTexasCardsFan@...  | 09/05/06

Question for TexasCardsFandmalbrough@...  | 06/04/07