Excel in Access — Access in Excel — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Excel in Access — Access in Excel

Get PDF file

by on
in The Office Tech Pro

Did you know that you can open up an Excel workbook in Access and an Access database in Excel WITHOUT creating a converted file. Sometimes the data we would like to analyze are in Access. While Access is a great tool for maintaining and reporting database information, Excel is the clear winner when it comes to analyzing data with formulas and charts. Likewise, if you have a big spreadsheet in Excel that you want to run a complex filter on, it might be better to run a query in Access. Here’s how to do it.

To open Access database in Excel (version 2003), use Data, Import External Data, Import Data. Select the Access database file name. If your database has multiple tables, you will have dialog box from which to choose the table you want to use. Although it might look like the data has been imported. It is linked to the Access database.

Say I go into the Access database and change Blue Yonder Airlines to reflect it’s new company name Blue Skies Airways. Then I choose the Refresh Data or Refresh All. Now you see the new name in the Excel workbook.

In 2007-10 you would use the Data tab, Get External Data group and click the From Access button. In both versions, you can simply right click on the worksheet and select Refresh Data.

Let’s go the other way, now. From Access (version 2003), use File, Get External Data and Link Tables. Select the worksheet where the data exists. Notice the difference in the way the table appears in the list. You actually see the Excel icon. The other difference is that the New Record button is deactivated. So your Excel workbook will keep its integrity. You will also find that you won’t be able to change or delete data. In 2007-10, you will use the External Data tab; and from the Import group, choose Excel.

Leave a Comment

Previous post:

Next post: