Databases - Using Data in MS Excel

There are many reasons to use data in MS Excel.

The integrated reporting package won't format the data the way you want it
The users are more experienced at using Excel
Users have Excel but not MS Access
Charts are easier in Excel

There are several ways to get the data.

Export the data as a comma separated text file and read that
Link directly to the data
Import the data
Use copy and paste

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.

Naming conventions - 8 characters max vs 20 vs 50
Field types - max number of characters in a string, no support for blobs (binary data), memos, etc.
Query language - there are many flavors of SQL

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

Data / Get External Data / Create new query...

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

I have to find a different available letter that is free on every machine
Link to it on ALL the machines that want to access the data
Edit the queries on ALL the spreadsheets which access that data.

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).

SELECT Products.ProductID, 
       Products.`Equipment/Parts`, 
       Items.`Serial Number`, 
       Items.BarCode, 
       Items.`Date Received`, 
       `Item Transactions`.`Location Barcode`
FROM   `T:\FullPath\Items`.`Item Transactions` `Item Transactions`, 
       `T:\FullPath\Items`.Items Items,
       `T:\FullPath\Items`.Products Products
WHERE  Items.ItemID = `Item Transactions`.ItemID 
  AND  Items.ProductID = `Item Transactions`.ProductID
  AND  Products.ProductID = `Item Transactions`.ProductID
  AND  Products.ProductID = Items.ProductID
  AND  ((`Item Transactions`.`Location Barcode`=?))
ORDER BY Products.ProductID

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).

Notes:

This query gets search criteria from cell B1. The ? causes Excel to use the criteria definition parameters attached to the spreadsheet.
Path.TableName space Name is the same as Path.TableName as Name
This creates an alias used to identify which table a field comes from.
MS Access uses square brackets ([..]) where Query Builder uses single quotes ('..'), though I have seen Query Builder work with square brackets ([..]) (when I copy and paste SQL from MS Access to Query Builder).

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

Data / Get External Data / Create New Query...

and uncheck

Use the Query Wizard to create/edit queries

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

View / Query

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

Data / Get External Data / Parameters...

will allow you to specify whether

The criteria (parameter) is hardcoded
The criteria comes from a cell on the spreadsheet
Excel will prompt you for a criteria

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.)

Definitions

Inner Join	Returns records where both tables contain data
Outer Join	All the records from one table are returned and related data from the second table is returned if there is any

SUM

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

TRANSFORM Sum(Nz([Vendor Jobs]![Actual Cost])+Nz([Vendor Jobs]![IDT Cost])+
              Nz([Vendor Jobs]![Proposed Cost])) AS [Total Cost]

NZ tells Access to treat null values as zeroes.
Within a single record, 3 fields are added and the results from several records are summed.

The same fragment, modified to work in Query Builder.

TRANSFORM Sum([Vendor Jobs]![Actual Cost])+Sum([Vendor Jobs]![IDT Cost])+
          Sum([Vendor Jobs]![Proposed Cost]) AS [Total Cost]

NZ is not recognized by Query Builder. (It is also not needed.)
This time, you need to Sum the individual fields separately and then add them.

Crosstab Query

Example of a crosstab query - formatted to be more readable. Extra spaces and returns don't seem to make a difference.

TRANSFORM Sum([Vendor Jobs]![Actual Cost])+Sum([Vendor Jobs]![IDT Cost])+Sum([Vendor Jobs]![Proposed Cost]) 
  AS [Total Cost]  

  SELECT [Graphic Requests].Distribution
    FROM `C:\Long\Path\CS`.`Graphic Requests` `Graphic Requests`, 
         `C:\Long\Path\CS`.`Vendor Jobs` `Vendor Jobs` 

   WHERE `Graphic Requests`.`GR Job #` = `Vendor Jobs`.`GR Job #`
     AND ((`Graphic Requests`.FY>'1997'))  

GROUP BY [Graphic Requests].Distribution  
   PIVOT [Graphic Requests].FY;

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.

Problems

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

Insert / Name / Define...

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 - clemenzi@cpcug.org