Access Databases

Import Data

You can import database tables from most database formats such as Access as ODBC databases.
(Data > Import External Data > Import Data)
This will display the "Select DataSource" dialog box.
locate the database file
Select the Table.
You can only import one database table at a time.
The contents of the entire table will be imported into the worksheet.
If you want to import a subset of data then you must create a database query

When you import data from a database table Excel automatically creates a "database query definition" file with the specified values.
For example if you import the Customers table from the Northwind database
Excel will create a query file called "NwindCustomers.odc".
The query file contains information about the database and the selected table
If you want to import the same database table again you can simply select the query file.

External Data Ranges properties dialog allows you to create a query file.

Location - if the active sheet already contains data, the data is shifted to the right and the necessary columns are inserted.

New Database Query

If you don't want to import a whole table but just a subset of the table you can use a database query.

Open a workbook and on the active sheet in cells A1 enter Start Date and in B1 enter the date 01/01/2003. In A2 enter End Date and in B2 enter the date 02/02/2003.

You must first specify the DNS (or Data Source Name).
Take the menu options Data>Import External Data>New Database Query

The easiest way to create a database query is to use the Query Wizard
The Query Wizard links directly to MS Query but takes you through the process.

To craete a query you obviously need access to the database
You will also need an ODBC data source for the database
An ODBC data source contains drivers that enable you to connect to the database as well as important connection information.
Different types of databases require different ODBC data sources.
Access databases automatically create a ODBC data source for you.

You will fire from here a dialog asking for your selection of an external datasource. We have chosen 'MS Access Database'.

Navigate your way to your Access database and select the table or query you want from the list displayed and add the fields you require, as below. (Remember, if you have a parameter query in Access already, this will create an error if we try to use it in Excel. 'Too few parameters, expected 1') .

Move on three screens making no changes until you arrive at the final screen (below). Take the second option to 'View data or edit query in Microsoft Query'. This will launch Microsoft Query. (For those familiar with Access, this looks very similar to the query designer).

From the image below you can see we have shown the 'criteria grid' by selecting View>Criteria from the MS Query menus. In our example we are going to take orders with a ship date between two dates, (01/01/2003 and 02/02/2003). To do this we enter the operator 'Between' followed by our first parameter. These are enclosed in square brackets and what is in here will, in certain circumstances, appear as the prompt in the input box, with the entry being the parameter. 'Between [Enter the start date]'. The next part is the 'And' operator followed by our second parameter, completing the parameter thus: Between [Enter a start date] And [Enter an end date]

In MS Query select File>Return data to Microsoft Excel. You will be prompted for your two parameters. (Start and end date), but you can ignore them. (Answer OK). You will now get the dialog below, asking for the positioning on the sheet.

Click the 'parameters...' button to show the dialog below. You now have three choices. 1) Prompt for the values. (You can enter any prompt here). 2) Use the following value. (You can enter a static value). 3) Get the value from the following cell (Our example). Remember to set how the value is obtained for all values and, if you want the data to update each time you change the value of the cell(s), then select the checkbox. (Against each value again).

Click OK in the 'Parameters' dialog and select $A$4 as the cell for the start of the data, click 'OK' in the 'import data' dialog and your data should flow in filtered between the two dates supplied. Each time you change the dates, the query is refreshed with the new input.
If you find you wish to change parameters or the way they action at a later date, this can be done in Excel via 'Data>Import External Data>Parameters...' or via the 'External data' toolbar. (Above)