Excel Power Pivot & Power Query For Dummies
Book image
Explore Book Buy On Amazon
Because Microsoft Access has traditionally been made available with the Microsoft Office suite of applications, Access databases have long been used by organizations to store and manage mission-critical departmental data. Walk into any organization, and you will likely find several Access databases that contain useful data.

Unlike SQL Server databases, Microsoft Access databases are typically found on local desktops and directories. This means you can typically import data from Access without the help of your IT department.

Open the Power Pivot window and click the From Other Sources command button on the Home tab. This opens the Table Import Wizard dialog box, shown here. Select the Microsoft Access option, and then click the Next button.

Open the Table Import Wizard and select Microsoft Access.

The Table Import Wizard asks for all the information it needs to connect to your database.

Provide the basic information needed to connect to the target database.

On this screen, you need to provide the information for these options:

  • Friendly Connection Name: The Friendly Name field allows you to specify your own name for the external source. You typically enter a name that is descriptive and easy to read.
  • Database Name: Enter the full path of your target Access database. You can use the Browse button to search for and select the database you want to pull from.
  • Log On to the Database: Most Access databases aren't password protected. But if you're connecting one that does require a username and password, enter your login credentials.
  • Save My Password: You can select the check box next to Save My Password if you want your username and password to be stored in the workbook. Then your connections can remain "refreshable" when being used by other people. Keep in mind that anyone can view the connection properties and see your username and password.

Because Access databases are essentially desktop files (.mdb or .accdb), they're susceptible to being moved, renamed, or deleted. Be aware that the connections in your workbook are hard coded, so if you do move, rename, or delete your Access database, you can no longer connect it.

At this point, you can click the Next button to continue with the Table Import Wizard.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

This article can be found in the category: