very slow query / reading SQL server linked tables

[Gerard1]

My situation:
I wrote a bunch of complicated queries in Access 2000 that read linked SQL Server 2000 tables. For the most part, these tables have hundreds or a few thousand records and performance is good. Some of the queries use vba functions. The queries with the vba functions run so slowly as to not be usable.
I can copy all the tables from SQL Server and make them Access tables and then the queries run well.
I seem to remember something in the MS knowledgebase that admitted to a problem that exists that causes this to happen. But now I cannot find any info and I wonder if this 'bug' has ever been fixed or if there's a better workaround solution?

Thanks for any advice.

 

[PHV]

Have you tried to play with PassThru queries (your VBA functions calls have to be translated to ms-sql equivalent calls) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gerard1]

Pass Thru query? I will investigate...
Thanks

 

[markgrizzle]

You'll need to use pass through queries, and rewrite your access queries in transact sql to get the full performance benefit of the sql server.

Otherwise, Access downloads all the data (or maybe just all the keys), then does its work at the speed of your local machine.