Databases Reference
In-Depth Information
TABLE 7.3
Common SQL Comparison Operators
Operator
Description
=
Equal
<>
Not equal to
!=
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
Such a query can be constructed utilizing the LIKE operator and the '%' wildcard character.
The '%' can be utilized in a text query to indicate
character. Thus, a search for zip codes
beginning with “063” would utilize “063%%,” or, in the case of a nine-digit zip code, “063%%-
%%%%”. Here is a sample LIKE query for this example:
any
“Select * From Suppliers Where ZIP LIKE '063%%'”
Another Example that will return records from the sample database is
“Select * From Suppliers Where STATE Like 'M%'”
This query ends up returning the following companies: Howard Miller, Chelsea Clock Company,
and Sligh Furniture Company. All of these companies are located in either Massachusetts (“MA”)
or Michigan (“MI”), which matches the query request.
7.5
USING THE MICROSOFT ODBC ADD-IN FOR MICROSOFT
EXCEL (XLODBC.XLA)
The connection examples earlier in the chapter utilized the QueryTables Collection Object to return
information queried from a Microsoft Access database by means of ODBC. The QueryTables
Collection Object is somewhat limited in its capabilities. Fortunately, Microsoft has constructed
an add-in for Excel that allows developers to harness the power of ODBC utilizing a few powerful
functions. The XLODBC.XLA ODBC add-in allows developers to create macros that interact
directly with ODBC through the ODBC driver manager.
In order to use these functions, the XLODBC.XLA must be added as a library reference
within the project. If it is not added as a library reference, the error message “Sub or function
not defined” will occur when an XLODBC.XLA function is called. To add it as a library reference,
do the following (Figure 7.10):
1.
In a
Visual Basic Module,
click References in the Tools menu.
2.
In the References dialog box, click to select the XLODBC.XLA checkbox.
NOTE:
If XLODBC.XLA is not present in the References dialog box, it can be added by
clicking the Browse button and locating the XLODBC.XLA file. The XLODBC.XLA
add-in should be located in the EXCEL\LIBRARY\MSQUERY or \PROGRAM
FILES\MICROSOFT OFFICE\OFFICE\LIBRARY\MSQUERY folder.
A properly installed XLODBC.XLA Reference is shown in
REF _Ref112138066 \h \*
MERGEFORMAT
(Figure 7.11). Notice that, under References, XLODBC.XLA is checked, and
