Well, this newsgroup is not about ODBC linked tables, so you should ask in
the m.p.access.odbcclientsvr newsgroup.
This is the expected result (going a little slower) when upgrading to
SQL-Server and using ODBC linked tables. First thing to check, make sure
that all the primary keys are correctly defined for each tables (often, more
or more primary keys are missing after the upsizing process) and that if you
have multiple indexes for a table, that the primary key is clusterized and
if it's not, that there are no other index clusterized and that the name for
the primary key is the first one in alphabetical order. See:
ttp://msdn2.microsoft.com/en-us/library/bb188204.aspx
And for Views:
http://support.microsoft.com/kb/q209123/
The second step would be to reindexe everything or at least, update the
statistics: sp_updatestats or
DBREINDEX on all tables. After that, clear the procedure cache
BCC
FREEPROCCACHE, DBCC DROPCLEANBUFFERS.
If it's still too slow after that, you will have to dig further: use
Terminal Server is you are on a very slow LAN or on a VPN, change the design
of your frontend in order to retrieve the minimal number of records (this is
particularly important when the user first open a form whose default
behavior is to retrieve *everything*).; create and use Views (see the
reference above), use passthrough queries (big default: they are read only),
create your own recordsets or use unbound forms (good speed, problem: lot of
work to do), switch to ADP (more work than ODBC linked tables but less than
with unbound forms) or even better, switch to .NET (obviously, the fastest
of all but also, the one that will require the more work to do, by far and
large).
For ADP, take a look at all the previous posts in this newsgroup; for
unbound forms, take a look at the books from Mary Chipman and Baron or from
Klander, for creating your own recordsets, see
http://support.microsoft.com/?kbid=281998 .
