Andreas Lauffer <a.lauffer@easysoft.de> wrote in message news:<3ijulvk01f2lk1chiajomp62rvs4c13bc9@4ax.com>. ..
> I changed from Access97 to AccessXP and I have immense performance
> problems.
>
> Details:
>
> - Access XP MDB with Jet 4.0 ( no ADP-Project )
> - Linked Tables to SQL-Server 2000 over ODBC
>
> I used the SQL Profile to watch the T-SQL-Command which Access ( who
> creates the commands?) creates and noticed:
>
> 1) some Jet-SQL commands with JOINS and Where-Statements are
> translated very well, using sp_prepexe and sp_execute, including the
> similar SQL-Statement as in JET.
>
> 2) other Jet-SQL commands with JOINS and Where-Statements are
> translated very bad, because the Join wasn´t sent as a join, Access
> collects the data of the individual tables seperately.
> Access sends much to much data over the network, it is a disaster!
>
> 3) in Access97 the same command was interpreted well
>
> Could it be possible the Access uses a wrong protocol-stack, perhaps
> Jet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server or
> Jet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead of
> Jet to ODBC and ODBC direct to SQL-Server
>
> Does anyone knows anything about:
>
> - Command-Interpreter of JetODBC, Parameters, how to influence the
> command-interpreter
> - Protocol-Stack of a Jet4.0 / ODBC / SQL-Server application
>
> Thanks , Andreas


Poor Andreas

I am unfortunately a bit of an expert in this area. My call : stick
with access 97. Unfortunately, this produce is getting a bit old.

What happened is that when Access 2000 was built, VBA was thrown away,
and VB6 grafted on to the product. In the process, the JET/ODBC SQL
parser was rebuilt (since you can use VB in SQL in Access), but not
tuned to the same degree (in fact, read : hopelessly broken).

There are many posts about speed problems 97 to 2000/XP, but most
concentrating on obscure and mainly irrelevant settings.
The only way to successfully use ODBC SQL with Access 2000/XP i