Table (linked to SQL) search very slow

[fmientjes]

I moved a table from Access 2003 to SQL 2000.
I often use ctrl-F (find) within tables to search specific records.
Now the table is linked to a sql server database, searches are very slow (mostly without result). Sorting the column and using the scrollbar is quicker! No problems with the Access table (which is linked to antoher Access database).
I have created indexes on sql, but no improvement in Access.
All other actions perform very well.

Is there a solution for this?

Frans

 

[nabiS2K]

It will be far faster for you to use temporary queries than to use the Find method. I would strongly recommend using queries. This is faster on Access tables as well.

 

[cmmrfrds]

If these are odbc linked tables then the find is doing a client side search which requires loading all the index data from sql server to the client. Better to use pass-through queries and let the find happen on sql server and only return the results of the find to the client.

 

[KZPools]

I'm experiencing a similiar problem today. Over the weekend I converted our AccessXP to SQL2000 (43 tables some with a hanful of records and some over 300,000 records). If we open the "Customers" form, linked to the "Customers" table, it opens all the customer records (less than 5,000 customers) to a new empty record. With the navigators we can move on the most recent record in the list. If we want to find the customer's Job Supervisor, we may search for the specific customer record provided we know the job number. The search is taking forever. I understand what is being said that the Access DB is doing a client side search of the SQL server's data. What does a pass-through query achieve? How does that affect the underlying recordsource and the ability to add data to the tables? In the meantime, I'm going to continue and browse other related topics for additional assitance but more feedback here would be appreciated. Thank you.

 

[nabiS2K]

A pass-through query is an entirely SQL driven query (no pretty UI) that skips Access's internal query syntax checking and sends the SQL statement directly to the datasource, in this case SQL Server. As a result, the query returns records as fast as possible.

 

[BNPMike]

K2Pools

Your error here is connecting your form to a table. You should never do this. Always decide what set of data you need and get just that. I can't believe you ever want a form that shows 5000 customers. If you want to add a new customer then do it via SQL which is always faster than trying to manipulate recordsets. If you want a partiuclar customer, then get the dialogue to capture that. If you want people related to a job number then capture that.

fmientjes
Same applies to you. You seem to have been using Access in a very inefficient way. Modern hardware and networks can disguise this but then you get caught out. Always try and write logical selects. It's good practice.