Use the PowerPivot Add-in for Excel to import data from Microsoft Access.
This video is available with closed captions. To view closed captions, click CC on the video control bar.
In this video we show you how to import data from an Access Database using both the Table Import Wizard and using a query.
Let’s get started.
The first step is to find and connect to an Access Database.
Then we’ll choose which data to import. I have a link to the Sales Database.
To import that database, I’ll open up the PowerPivot Window.
On the PowerPivot Ribbon click From Database and select From Access.
This launches the Table Import Wizard which guides me through setting up a connection to my Data Source.
I click browse and navigate to the location on my network where the file is saved.
Select ContosoSales and click Open.
I want to select from a List of Tables And Views and when I click Next, the Wizard displays list of all the Source Tables within my database.
I select all the tables and start the import by clicking Finish.
When I click Close, the data is displayed as six new tables in my PowerPivot Workbook.
My co-worker sent me a query to use to import just the data I need from the ProductCategories Database File.
The ProductCategories File is in Access. So I begin by clicking From Database and selecting From Access.
I click Browse and navigate to the location on my network where this file is saved. I select ProductCategories and click Open.
This time I opt to write a query. Click Design and then Import.
I navigate to the location on my computer where I saved the query and open it.
The SQL Statement appears in the window. This query selects Sales Data for all Contoso products except music, movies, audio books, toys, and games.
I make sure the statement is valid and then Import the data.
The data is displayed as new table in my PowerPivot Window. I rename it ProductCategory.
I can keep this data current by manually refreshing.
If the contents of the Access Database change, refreshing keeps my PowerPivot data up-to-date.
Refreshing only works as long as PowerPivot can access the source file.
For more information watch the Refresh Video or read the Refresh Documentation.
We’ve added data from our Corporate Access Database using the Table Import Wizard.
We then used a query to Import External ProductCategory data.
I recommend watching the videos in this series in order, especially if you’re following along using your own installation of PowerPivot for Excel.
Some of the steps in one video build upon steps taken in a previous video.
Thank you for taking the time to view this video. I hope you found it helpful.