How to Query an Access Database Tables from Excel 2019

To make an external data query to an Microsoft Access database table, you click Data→  Get Data→  From Database→  From Microsoft Access Database on the Excel Ribbon or press Alt+APNDC. Excel opens the Import Data dialog box, where you select the name of the Access database (using an *.mdb file extension) and then click the Import button.

After Excel establishes a connection with the Access database file you select in the Import Data dialog box, the Navigator dialog box opens. The Navigator dialog box is divided into two panes: Selection on the left and Preview on the right. When you click the name of a data table or query in the Selection Pane, Excel displays a portion of the Access data in the Preview pane on the right. To import multiple (related) data tables from the selected Access database, select the Enable Multiple Items check box. Excel then displays check boxes before the name of each table in the database. After you select the check boxes for all the tables you want to import, you have a choice of options:

• Load button to import the Access file data from the item(s) selected in the Navigator directly into the current worksheet starting at the cell cursor’s current position
• Load To option on the Load button’s drop-down menu to open the Import Data dialog box where you can how you want to view the imported Access data (as a worksheet data Table, PivotTable, PivotChart, or just data connection without importing any data) and where to import the Access data (existing or new worksheet) as well as whether or not to add the Access data to the worksheet’s Data Model
• Transform Data button to display the Access data table(s) in the Excel Power Query Editor where you can further query and transform the data before importing into the current Excel worksheet with its Close & Load or Close & Load To option

When you select the Load To option to specify how and where to import the Access data, the Import Data dialog box contains the following option buttons:

• Table option button to have the data in the Access data table(s) imported into an Excel data table in either the current or new worksheet — see the “Existing Worksheet” and “New Worksheet” bullets that follow. Note that when you import more than one data table, the Existing Worksheet option is no longer available, and the data from each imported data table will be imported to a separate new worksheet in the current workbook.
• PivotTable Report option button (the default) to have the data in the Access data table(s) imported into a new pivot table that you can construct with the Access data.
• PivotChart option button to have the data in the Access data table(s) imported into a new pivot table with an embedded pivot chart that you can construct with the Access data.
• Only Create Connection option button to create a connection to the Access database table(s) that you can use later to actually import its data.
• Existing Worksheet option button to have the data in the Access data table(s) imported into the current worksheet starting at the current cell address listed in the text box below.
• New Worksheet option button to have the data in the Access data table(s) imported into new sheet(s) that’s added to the end of the sheets already in the workbook.
• Add This Data to the Data Model check box to add the imported data in the Access data table(s) to the Data Model already defined in the Excel workbook using relatable, key fields.
• Properties drop-down button to open the drop-down menu with the Import Relationships Between Tables check box (selected by default) and Properties item. Deselect the check box to prevent Excel from recognizing the relationship established between the data tables in Access. Click the Properties button to open the Connection Properties dialog box, where you can modify all sorts of connection properties, including when the Access data’s refreshed in the Excel worksheet and how the connection is made.

The image below shows you a new Northwind Customer Orders workbook after importing both the Customers and Orders data tables from the sample Northwind Access database as new data tables on separate worksheets. When the two data tables were imported, Excel automatically added two new worksheets (Sheet2 and Sheet3) to the workbook, while at the same time importing the Customers data table to Sheet2 (which I renamed Customers) and the Orders data table to Sheet3 (renamed Orders). Sheet1 (which was blank) was deleted prior to taking the screenshot.

The following image shows same new workbook, this time with the Orders worksheet selected and the Manage Relationships dialog box open (by clicking the Relationships button on the Data tab or pressing Alt+AA). When Excel imported these two data tables, it automatically picked up on and retained the original relationship between them in the Northwind database, where the CustomerID field is the primary key field in the Customers data table and a foreign key field in the Orders data table.

After importing the external data into one of your Excel worksheets, you can then use the Filter buttons attached to the various fields to sort the data and filter the data.

After you import data from an external source, such as a Microsoft Access database, into a worksheet, Excel automatically displays a Queries & Connections task pane with two tabs: Queries, which displays the source(s) of the data imported into the current workbook, and Connections, which displays their connection to the workbook Data Model (and to each other if there are multiple sources and they are related to each other). If this task pane is not currently displayed in the current worksheet, click Data→  Queries & Connections (or press Alt+AO) to redisplay it.

Excel keeps a list of all the external data sources and data queries you make to the current workbook so that you can reuse them to import updated data from another database or web page. To quickly reconnect with a data source, click the Recent Sources button on the Data tab (Alt+PR) to open the Recent Sources dialog box where you click the name of the external file before you select the Connect button. To reuse a query, click the Existing Connections button on the Data tab (Alt+AX) to open the Existing Connections dialog box to access this list and then click the name of the query to repeat before you click the Open button.