Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

previousPrevious article   Next articlenext back to categoryAccess articles

article Discover How To Link An Excel Spreadsheet To An Access Database

Discover how to link an Excel spreadsheet to an Access database as a linked table and use queries, forms and reports with this linked table. Any updates to the original data in Excel data will automatically update in Access. Read on to find out more.
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.

Author is a freelance copywriter. For more information on microsoft access training london, please visit http://www.microsofttraining.net


Original article appears here:
http://www.microsofttraining.net/article-1409-how-link-excel-spreadsheet-access-database.html


Back to article list

Distribution notes

PUBLICATION GUIDELINES

  • You have permission to publish this article for free providing the "About the Author" box is included in its entirety.
  • Do not post/reprint this article in any site or publication that contains hate, violence, porn, warez, or supports illegal activity.
  • Do not use this article in violation of the US CAN-SPAM Act. If sent by email, this article must be delivered to opt-in subscribers only.
  • If you publish this article in a format that supports linking, please ensure that all URLs and email addresses are active links, without the rel='nofollow' tag.
  • Software Training London Ltd. owns this article. Please respect the author's copyright and above publication guidelines.
  • If you do not agree to these terms, please do not use this article.


Microsoft Office courses London
Course rating:
4.7 stars - based on 829 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Facebook · Twitter · Google+ · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom