Importing Data from Access Databases


You have data in a Microsoft Access database that you'd like to import into Excel for analysis.


Use File images/U2192.jpg border=0> Open... to open the Access database file.


When you select File Open..., youll get the familiar Open dialog box. In the "Files of type" drop-down listbox, look for and select the Access Databases file type, as shown in Figure 3-8. Access database files have the file extension .mdb.

Locate and select the database file you'd like to open and then press the Open button. Upon doing so, you'll see the Select Table dialog box shown in Figure 3-9.

The example database file I'm using here contains standard structural material properties and standard structural shape data. It includes two distinct tables: Materials and Sections. In this case, I've selected the Materials table for import into Excel. When I make the table selection and press the OK button, the data contained in the table is imported into Excel, as shown in Figure 3-10.

That's all there is to it. You can now perform operations on the imported data as you wish.

I should point out that the data imported into Excel is linked to the original Access database. This means that when data is changed in the original database, it can automatically be updated in your spreadsheet. You have some control over how the data is updated. Notice the External Data toolbar , shown in Figure 3-10, to the right of the imported data. This toolbar appears automatically when the data is imported. (You can also show this toolbar from the main menu bar via View images/U2192.jpg border=0> Toolbars images/U2192.jpg border=0> External Data.)

Figure 3-8. Opening an Access database

Figure 3-9. Select Table dialog box

The second button from the left of the External Data toolbar is the Data Range Properties button . Pressing this button opens the External Data Range Properties dialog box , which allows you to specify how often you want Excel to automatically refresh the imported data. You can also specify that you want the data automatically refreshed every time you open your spreadsheet. The button with the exclamation point on it forces Excel to refresh the data instantly.

Figure 3-10. Imported Access table

You can import data from other databases as well. For example, Excel allows you to create new database queries facilitating access to such databases as Oracle, FoxPro, and dBase. Select Data Import External Data images/U2192.jpg border=0> New Database Query... to create a new database query or use an existing data source. See the help topics "About importing data," "Create an OLAP query file to connect to an OLAP database," and "Import or connect to data in Excels online help" for more information.


See Also

Take a look at the help topic "Data sources you can access" for more information on the types of databases you can access from within Excel and for specific information on connecting to those databases.

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data


Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations


show all menu

Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: