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.
The Table Import Wizard asks for all the information it needs to connect to your 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.