Connect to an Access database

You can create a dynamic connection between an Access database and your Excel workbook. You can then refresh the connection to get updates from the database.

Newer versions Office 2010 – 2013 Office 2007



Use Excel's Get & Transform (Power Query) experience to connect to Access databases. You can import tables or queries, and transform your data if needed.

1. Click the Data tab, then Get Data > From Database > From Microsoft Access Database. If you don't see the Get Data button, click New Query > From Database > From Access.

2. In the Import Data dialog box, browse for or type a file URL to import or link to a file.

3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

4. Click Load or Edit.

Note: You can also restore the Legacy Connectors to mimic earlier behavior. See the section on "How do I restore the legacy Get External Data experience?" in the following article: Unified Get & Transform.

In Excel 2010 and 2013, there are two ways to get data from an Access database: the Get & Transform (Power Query) experience, or the Data Connection Wizard.

Get & Transform

1. In the Power Query ribbon tab, select From Database > From Access Database.

   

2. In the Browse dialog box, browse for or type a file URL to import or link to a file.

3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

4. Click Load or Edit.

Note: Make sure you have downloaded and installed the Power Query Add-In.

Data Connection Wizard

1. On the Data tab, in the Get External Data group, click From Access.

   

2. In the Select Data Source dialog box, browse to the Access database.

3. In the Select Table dialog box, select the tables or queries you want to use, and click OK.

4. You can click Finish, or click Next to change details for the connection.

5. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

6. Click the Properties button to set advanced properties for the connection, such as options for refreshing the connected data.

7. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.

8. Click OK to finish.

Important: 

• While trying to import data from a legacy Excel file or an Access database in certain setups, you may encounter an error that the Microsoft Access Database Engine (Microsoft.ACE.OLEDB.12.0 provider) is not registered on the local machine. The error occurs on systems with only Office 2013 installed. To resolve this error, download the following resources to ensure that you can proceed with the data sources you are trying to access.

  • Microsoft Access Database Engine 2010 Redistributable

  • Access Database Engine 2010 Service Pack 1

Note: Power Query isn't supported in Excel 2007, but you can use the Data Connection Wizard to connect to an Access database.

1. On the Data tab, in the Get External Data group, click From Access.

   

2. In the Select Data Source dialog box, browse to the Access database.

3. In the Select Table dialog box, select the tables or queries you want to use, and click OK.

4. You can click Finish, or click Next to change details for the connection.

5. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

6. Click the Properties button to set advanced properties for the connection, such as options for refreshing the connected data.

7. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.

8. Click OK to finish.

Keep the data in your workbook up to date

Now that you're connected to your Access data, you'll always want the latest data in your workbook. Go to Data > Refresh All to get the latest data. For more information on refreshing, go to refresh an Access data connection.

Important: Connections to external data might be disabled on your computer. To connect to data when you open a workbook, enable data connections by using the Trust Center bar, or save the workbook in a trusted location.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources