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

Unfortunately, I am a bit of an expert on this topic. In short : stay
with Access 97.

There are quite a few posts on the net dealing with slow performance
upgrading Access 97 to 2000/XP. Nearly all of them focus on obscure
and mainly insignificant recordset properties, etc.

The real problem is that when Access 2000 was built, VBA was thrown
away and VB6 grafted on to Access. In the process, the Jet SQL parser
was rewritten (since you can use VB operators in SQL statements in
Access), but not fine tuned in the same way as Access 97 (read :
hopelessly broken for all but the simplest queries)
So it is simply not possible to get the same performance in 2000/XP
using ODBC.
The MS solution is to use the ADP format project, which throws away
JET and uses SQL server direct, also throwing away most of the reasons
you would want to use MS Access.
You can use ADO to connect in code, and possibly use that to pass
recordsets to forms, but its a huge workaround.

As an example of problems with the 2000/XP parser, you can try
executing a query on an ODBC linked table :

SELECT IDValue FROM VeryLargeTable WHERE IDValue=25

this should be instant if IDValue is the indexed PK.
now write

public function Return25 () as long
Return25 = 25
end function

and try

SELECT IDValue FROM VeryLargeTable WHERE IDValue=Return25()

On my database, in Access 2000/XP the first ran instantaeously, the
second took 19 seconds (clearly a full table scan as you relate in
your post).
In Access 97, both run instantaneously.

So : my conclusion, after much testing and hair tearing -- its busted.
RIP ODBC.
If you find any magic tricks to cure these ills PLEASE let me know
(post here !).
Failing that, I'll eagerly await Access 2060 to see if the problem is
fixed. Unfortunately, the longer we wait, the less chance of this
happening, I think!

As an aside, Sagekey software make a great Wise/Installshield install
script for about $350 US which installs the Access 97 runtime and
allows it to coexist with any other version of Access (2, 95, 97,
2000, XP). You can bundle your A97 databases with this and then the
client doesn't have to worry about having Office 97 anymore.
This is what I do.

ODBC/Jet works fine in Access 97, and what's more, i _like_ it. It's
fast and has a lot of advantages. You get two databases for the price
of one (a front and back end database engine, no less, talk about
distributed computing !).
VIVA LA ACCESS 97 !!

All in all, MS have kneecapped ODBC development in new versions of
Access. I'm getting ready to move to Visual Studio .NET, as they
probably want me to. Access was solving too many problems for its
price tag.

commiserations,

Ben McIntyre
Self confessed Access 97 fan.