Databases Reference
In-Depth Information
GetFieldNames = TotalFieldNames
Next i
'Close the connection to the database
SQLClose (Chan)
Exit Function
No_Table:
MsgBox SQLOpenString, vbOKOnly + vbCritical, "Error: The
Referenced Table Does Not Exist in "
GetFieldNames = 0
'Close the connection to the database
SQLClose (Chan)
End Function
This function can be easily tested utilizing the following
code snippet:
Sub TestGetFieldNames()
Dim Names()
Debug.Print GetFieldNames("DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:\temp\ADA\MCS.mdb", "Suppliers", Names())
For i = 1 To UBound(Names)
Debug.Print i; Names(i)
Next i
End Sub
Doing so will produce the following output using the MCS.mdb test database included on the CD-
ROM. The output is a comprehensive listing of all the Field names contained in the Suppliers Table.
1.
NAME
2.
MANUFACTURER
3.
ADDRESS
4.
CITY
5.
STATE
6.
ZIP
7.
ZIPPLUS4
8.
PHONE
9.
FAX
10.
WEBSITE
7.6
CONSTRUCTING A DATABASE QUERY TOOL
Using the functions presented and the XLODBC add-in, it is possible to construct a generic database
query tool that will illustrate complex query use within the Excel environment for data mining
purposes. The sample application can be run by selecting ADA->Chapter 7->Access SQL Query,
which will bring up the GUI shown in Figure 7.12.
The basic elements of the sample database query tool can be adapted for any custom application
the reader has in mind. The sample tool allows the user to point to a particular Access file (*.mdb)
of interest. Once a file has been selected, a valid query must be constructed. The GUI aids in this
endeavor by first instructing the user to select a Table of interest within the chosen database. From
there the user chooses the Fields that are to be operated on within the database. The user may also
choose to add a Where clause to the given query by selecting the Condition 1 checkbox. If the user
