About a month ago, I sent a post to the list about a problem we were having
with a query in MS Access.  (See copy of message below)...

Thanks to folks at the HP Response Center and their persistence, we now
have a solution.  The MS Jet database engine files on the machine that
produced efficient SQL were version 2.5.  However, the MS Jet database
engine files on the machine producing inefficient SQL were version 2.0.
The MS Jet database engine files can be upgraded by downloading and
installing the MS Access version 2.0 Service Pack (accsvc.exe) from
Microsoft's web site.  The accsvc.exe file is self-extracting.  Run the
setup file extracted from accsvc.exe to install the Service Pack.
Installing the Service Pack on the machine producing inefficient SQL
corrected our problem.

There is a possible drawback to installing the MS Access ver 2.0 Service
Pack.  Microsoft's Knowledge Base contains an article on "How to Remove the
MS Jet 2.5 Database Engine."  Since the article did not explain why one
would want to remove the Engine, we contacted Microsoft's support staff.
The reason is that there have been several calls logged for problems in MS
Access running Action Queries with Parameters after the Service Pack is
installed.



*Original Post:

(Subject: MS Access/ODBC Problem)

>We have encountered a problem with a Microsoft Access query
>using the HP Allbase/SQL driver and would be interested in
>any insight that might help us find a solution.
>
>Here is a description...
>
>The query selects fields from Files A, B, & C, joined (A to B,
>B to C) by an id field.  The selection criteria comes from
>fields in Files A & B.  When we run the query on one machine,
>there is one SQL Select statement which includes all the joins &
>criteria and returns the desired records.  When the same query
>is run on another machine, the result is 3 SQL Select statements:
>1)Select from File A with criteria;  2)Select from File B with
>criteria;  3)Select from File C (no criteria and therefore all
>records are processed).  This does return the same results as in
>the first case, however it is inefficient and takes considerably
>longer.
>
>To eliminate some of the variables, we have used the same logon in
>the data source on both machines, run the query from a floppy disk,
>and verified that the driver versions are the same.  Also, we
>determined the above SQL results based on log files generated by an
>ODBC tracing utility.  The SQL looks the same when viewed in
>Access...
>
>Thanks in advance for your help.
>Hans

______________________________
Hans J. Lapp
Data Support Specialist
Miami University
Oxford, Ohio  45056
(513) 529-7192

[log in to unmask]