Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Query Access Database Tables to Import External Data in Excel 2013

Excel 2013 makes it easy to import data into a worksheet from other database tables created with stand-alone database management systems (such as Microsoft Access), a process known as making an external data query.

To make an external data query to an Access database table, you click the From Access command button on the Ribbon’s Data tab or press Alt+AFA. Excel opens the Select Data Source dialog box where you select the name of the Access database and then click Open.

The Select Table dialog box appears from which you can select the data table that you want to import into the worksheet. After you select the data table and click OK in this dialog box, the Import Data dialog box appears.

The Import Data dialog box contains the following options:

  • Table to have the data in the Access data table imported into an Excel table in either the current or new worksheet — see Existing Worksheet and New Worksheet entries that follow

  • PivotTable Report to have the data in the Access data table imported into a new pivot table that you can construct with the Access data

  • PivotChart to have the data in the Access data table imported into a new pivot table with an embedded pivot chart that you can construct from the Access data

  • Only Create Connection to link to the data in the selected Access data table without bringing its data into the Excel worksheet

  • Existing Worksheet (default) to have the data in the Access data table imported into the current worksheet starting at the current cell address listed in the text box below

  • New Worksheet to have the data in the Access data table imported into a new sheet that’s added to the beginning of the workbook

You can see an Excel worksheet after importing the Invoices data table from the sample Northwind Access database as a new data table in Excel. After importing the data, you can then use the AutoFilter buttons attached to the various fields to sort and filter the data.

image0.jpg

Excel keeps a list of all the external data queries you make so that you can reuse them to import updated data from another database or web page. 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.

Databases created and maintained with Microsoft Access are not, of course, the only external data sources on which you can perform external data queries. To import data from other sources, you click the From Other Sources button on the Data tab or press Alt+AFO to open a drop-down menu with the following options:

  • From SQL Server to import data from an SQL Server table

  • From Analysis Services to import data from an SQL Server Analysis cube

  • From Windows Azure Marketplace to import data from any of the various marketplace service providers — note that you must provide the file location (usually a URL address) and your account key before you can import any marketplace data into Excel.

  • From OData Data Feed to import data from any database table following the Open Data Protocol (shortened to OData) — note that you must provide the file location (usually a URL address) and user ID and password to access the OData data feed before you can import any of its data into Excel

  • From XML Data Import to import data from an XML file that you open and map

  • From Data Connection Wizard to import data from a database table using the Data Connection Wizard and OLEDB (Object Linking and Embedding Database)

  • From Microsoft Query to import data from a database table using Microsoft Query and ODBC (Open Database Connectivity)

blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!