|
Search our FrontPage Support Area
FrontPage Support Area Site Map
The following documents are geared towards using VirtuFlex
with Microsoft FrontPage.
ODBC to a Microsoft Access Query
November 12, 1997
W. Curtiss Priest, Ph.D.
The Access "Jet Search Engine" supports ODBC calls to BOTH
TABLES and QUERYs present in the .MDB file.
This provides at least 3 uses:
1. A "defined field" may be constructed in the Query and used as if it exists in
the Table. This permits use of Access "functions."
The SQL for a defined field can be, say:
(Space(32-Len([Member Table].[LastName])) AS LNamePad In the
"Design" window the "Field" appears as:
LNamePad:(Space(32-Len([Member Table].[LastName]))
See footnote 1 below about VirtuFlex and Access "built-in"
functions.
2. The Access defined Query can be used just as it is (but see Rule #3).
3. The Access Query can be further qualified using tests -- but tests may be applied
to only those fields which do not have criteria in the original query (see Rule #2).
Rules --
1. One uses the name of the Query in place of the name of the TABLE.
2. One may NOT specify in the WHERE clause any fields already specified in the Query
3. But, one must have something in the WHERE clause because VirtuFlex passes WHERE
regardless if there is anything contained between the third and fourth colon in the
DBASE:SELECT.
So, if you want exactly the existing query, you can pick any field, not
already specified, and use: unspecified_fieldname like '%' .
And the output will be exactly the same records as the query [see footnote
3].
4. You may always print fewer fields in the VirtuFlex Interlace, but never any fields that
are not already printed by the original Query [see footnote 2].
Symptom of violating rule #1: No existing criteria in the Query are applied, and No
"defined fields" are visible to ODBC.
Symptom of violating rules #2 and #4: ODBC will return an error, such as:
"Too few parameters. Expected 1." [where the number of
"expected" (here 1) changes as follows].
The number of "expected" will equal the number of specifications of fields that
are already specified in the Query. In other words, for every
field you try to apply a criterion to, that already has a criterion in the original query,
you will get that many "expected" parameters (in the ODBC error) .
But also, the "expected" are the number of fields in the
interlace that are NOT output (printed) in the original Query.
However, if the specified field is the same as the unprinted field, the
"expected" is still 1 for that field.
By "specified" -- it is meant -- those fields that have search conditions
applied to them, called "Criteria" in the Access Design window (or are in the
WHERE clause of the, matching, SQL window).
Symptom of violating rule #3: ODBC will return the following error: "Syntax error in
WHERE clause"
Footnote 1:
While Access functions may be used in VirtuFlex, they cannot be "operated upon"
using other VirtuFlex macros. For example, while it is possible to return the length of
'LastName' in VirtuFlex, it is not possible to compute 32 minus the length in VirtuFlex
using, say EVAL:MATH because the macro is evaluated BEFORE the Access data is returned.
Footnote 2:
If you wish to use a criterion against a field in the Access Query, that field must appear
in the Query as a printed field. This is because Access does not permit selecting a field
that neither is printed nor has criteria. Such a field simply disappears from the Design
form.
Footnote 3:
Rule #3 applies not only to Access query criteria against constants, but also to query
criteria against variables.
For example, if the field LastName has a criteria against the variable
'lstnam' -- the criteria may appear as:
like [lstnam], or =[lstnam], which becomes simply [lstnam]
The existence of any criteria tests for a field means that the ODBC query
may not further test that field (and the "expected" ODBC error message will
appear if one attempts to test that field).
|
