Excel in Access-Access in Excel

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 is 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.
Microsoft Access 2007: Database Management Made Easy
To open an 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 a 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.


screencap


If I go into the Access database and change Blue Yonder Airlines to reflect its new company name, Blue Skies Airways, and then choose the Refresh Data or Refresh All, now you see the new name in the Excel workbook.


screencap


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.

Many people avoid using Access, Microsoft’s database-management application, in favor of Excel. But even Excel’s advanced features can’t handle all of your large data management tasks.

This is the perfect time to tackle Access because Access 2007 is easier to use than any previous version.

With Microsoft Access 2007: Database Management Made Easy, you can have a database up and running in no time. You’ll discover reports, forms and queries that get you the information you need right now … while still staying flexible enough to meet your changing needs. Learn more about this MS Access training...
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.



In this Executive Summary of our popular webinar – from presenter Melissa Esquibel, a Microsoft® Certified Trainer – you’ll learn just how easy managing all your data can be. This plain-English Executive Summary will help you get more out of Access than you ever dreamed you could.

You'll discover:access
  • The Access 2007 Interface. You’ll learn tabs, ribbons, buttons, security, QAT and the navigation pane.
  • What a database is. Melissa explains flat vs. relational databases, creating relationships, looking at your data objectively and database objects.
  • Getting data into Access. Melissa will review typing data into records; importing vs. linking; importing and linking from Excel, Outlook and other sources; and adding fields and tips for using the table interface.
  • Fast reports and forms. You’ll find how to create and modify forms and reports, use forms for more than data entry and add elements like headers, breaks and totals.
  • Basic queries and how to use them. This last section answers all your questions about queries.

Leave a Comment