Opening an Entire Access Table in Excel

To import an entire table created in Access (as opposed to a specific set of records from that table), click the Data tab, and then click From Access in the Get External Data group. Windows Vista will launch a search for files with the extensions .mdb, .mde, .accdb, and .accde. When you find the Access file you're looking for, select it, and then click Open. You'll be presented with the Select Table dialog box, which is shown in Figure 23-4. (Drag the lower-right corner of the Select Table dialog box if you need to see more of the Description column.)

Figure 23-4: When you click the From Access command to open an Access file, the Select Table dialog box asks you to choose which table you want to import.

For information about importing selected records from an Access (or other) database using Microsoft Query, see "Using Microsoft Query to Import Data" on page 759.

The Select Table dialog box actually lists not only tables from your Access file but certain views as well. (The items shown in Figure 23-4, for example, are all views from the Northwind database that Microsoft supplies as a sample file with Access.) If you open any Access view in Excel, you get all the records currently displayed by Access in that view. If you open a table, you import all the records in that table. In either case, the data you import becomes either a table or a PivotTable in Excel, depending on how you complete the Import Data dialog box (see Figure 23-2).

You can set refresh parameters for your imported Access data the same way as for any other data connection. For details, see "Setting Refresh Options" on page 747.

Note 

You can also import an Access table by clicking the Microsoft Office Button, clicking Open, and then selecting Access Databases from the list beside the File Name box. (The resulting table or PivotTable behaves as if you had used the From Access command on the Ribbon.) You cannot save an Excel range as an Access table, however.