Linking to Data via 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
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
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
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).
These comments are based on MS Office 97 running under Windows 98.
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 / QueryDouble 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
Inner and Outer Joins
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.)
|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|
From MS Access
TRANSFORM Sum(Nz([Vendor Jobs]![Actual Cost])+Nz([Vendor Jobs]![IDT Cost])+ Nz([Vendor Jobs]![Proposed Cost])) AS [Total Cost]
TRANSFORM Sum([Vendor Jobs]![Actual Cost])+Sum([Vendor Jobs]![IDT Cost])+ Sum([Vendor Jobs]![Proposed Cost]) AS [Total Cost]
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
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.
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.