Databases - Using Data in MS Excel

There are many reasons to use data in MS Excel. There are several ways to get the data.

Linking to Data via ODBC

Normally, links are via ODBC or ADO (the new method replacing and including ODBC).

ODBC works by defining an alias which stores all the connection parameters. Then any ODBC aware program (ie, almost anything you would want to develop with) only needs to know the name of the alias.

Normally, ODBC aliases are managed via Control Panel / ODBC Data Sources. In addition, several development packages provide their own ODBC management interfaces (Delphi and MS Access for example).

ODBC allows you to store your data in any type of database - MS Access, Oracle, Paradox, etc. In theory, at some future time, you can move the data to a different type of database, simply re-define the ODBC link, and your existing programs won't even know (or care) that the change was made.

Unfortunately, in practice, this usually won't work because of significant differences between databases.

Linking to MS Access Data

This is a joke. Whoever designed the Excel/ODBC/MS Access connection ...

Supposedly, data is read via an ODBC link. From the MS Excel menu, select

If the desired ODBC definition is already available, select it from the list. Otherwise, select <New Data Source>. (This actually creates a new ODBC alias without going through the Control Panel).

In the dialog box, enter a name that describes the data source. (Sometimes, I use filename.mdb for an MS Access database.) Set the driver type to Microsoft Access Driver (*.mdb) and press Connect.... Press Select... in the next dialog box to select the specific mdb file. Then press OK. All of the available tables and queries will now be in the Default Table pick list. (It does not matter if you select a table at this point.) Press OK 2 more times to close the dialog boxes and start the wizard.

In the wizard, you select the tables and fields that you want. The rest of the wizard is pretty straight forward - just answer the questions as appropriate.

ODBC - Yeah Right

Well, when you link to a database (mdb file), it appears that you are using ODBC. Specifically, you must select the database from a list of ODBC aliases.

However, it is painfully obvious that ODBC aliases are not actually used. Instead, the fully qualified path to the mdb file is extracted from the alias definition and that path is stored in Excel. (Hey, that's pretty worthless.)

Specifically, for my application, every computer which runs a specific spreadsheet MUST have the t-drive defined to point to a specific lan drive. If any one of the systems that I want to access this data does not have that drive letter available, then

Yeah, it appears that it is possible to edit the drive letters in SQL mode. However, any queries built using the wizard must be completely replaced - there is no way to edit them. (Boy, I hope that no one wants to add another machine to our group next month.)

Here is the actual SQL for an Excel query (with extra line breaks added to make it easier to read and a dummy path). Notice that it contains 3 linked tables and that each reference contains the full path to items.mdb (the mdb extension is not included).

The whole purpose of ODBC is so that you don't have to go through this crap. The application is supposed to point to the locally defined alias and not to a specific file. To my knowledge, Microsoft Excel is the ONLY program that violates this basic principle (but knowing Microsoft, every product that uses Microsoft Query (ie, the entire office suit) probably does this).


These comments are based on MS Office 97 running under Windows 98.

Microsoft Query

Well, the wizard is pretty good - however, it is useful to actually see the SQL. It is also useful for users to be able to modify the selection criteria without running the wizard.

Microsoft Office includes an optional (ie, you must load it first) query builder. The interface is very similar to the query builder that is a part of MS Access. (Similar but quite different.)

In order to use Microsoft Query, from the Excel menu, select

and uncheck

In order to add criteria, from the MS Query menu, select

Double click the Value field to get a very useful wizard. It will even list all the values currently associated with that field.

In Excel, the menu selection

will allow you to specify whether If that menu selection is not available, place a question mark (?) in the SQL. (See the example above.)

Inner and Outer Joins

Well, in MS Access, you edit the type of join by highlighting the link (by left clicking on it) and then right clicking it and selecting Join Properties.

In MS Excel, you double click the link. (No, this is not in the help.) Actually, double clicking also works in MS Access but I didn't know that until I "discovered" it in MS Excel.

Surprise, surprise - If an MS Excel query contains more than 2 tables you can NOT use an outer join. (That was not in the help either.) Um, it appears that MS Access has the same problem. The solution is to have multiple MS Access queries. (I can't figure out how to have multiple queries in MS Excel.)



SQL is anything but standard. Look at the 2 SQL fragments below (from a cross tab query). They both produce the same results.

From MS Access

The same fragment, modified to work in Query Builder.

Crosstab Query

Example of a crosstab query - formatted to be more readable. Extra spaces and returns don't seem to make a difference. Notice that part of the query uses square brackets ( [ .. ] ) and that part uses single quotes ( ' .. ' ). This happened because part of the query was copied from MS Access and part came from Query Builder. I combined them in notepad and pasted the result back into Query Builder - and it worked.

Once you save a crosstab query (which must be written in SQL) and close Query Builder, you will not be able to re-open the query and edit it. Therefore, be sure to save a copy so you can edit it latter.


Once the query is placed on the worksheet, there is no way to edit the location. I tried to use but I completely lost the query.

It is possible to copy SQL from MS Access to Query Builder. However, once you save the query and exit Query Builder, there is no way to edit the SQL - when you re-open the query, you must paste new SQL into the query, the old SQL is not available to be edited. When I open the Excel (*.xls) file in notepad, I can see the SQL. Also when I open the Query Builder (*.dqy) file in notepad, I can see the SQL. I just can't see/edit the SQL from within either program. (This is true with a crosstab query - it is probably not a problem with a simple Select query.) Therefore, I suggest keeping a copy of your query in notepad so that you can paste it into the query as needed.

Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / LinkToExcel.html