Solved

FIND function slow in Access 2003

Posted on 2004-08-21
7
343 Views
Last Modified: 2008-01-09
Hi,

Ive recently upgraded one of my large Access 2000 applications to 2003. It runs with a SQL server on the backend using linked tables.
As often as I tell them otherwise, my users prefer to do a find instead of a filter on certain forms. We have noticed that since the upgrade doing a find on an indexed field of a table since upgrading to 2003 has slowed enormously. The table in question has less than 40,000 records. Users stillusing 2000 on the same data are still much faster.
Anyone have any ideas?

Adam
0
Comment
Question by:adamy12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:gillgates
ID: 11862322
Use pass through query... in the query view go to "Query" -> "SQL Specific" -> "Pass Through"

This will do all the work on the server and not bring back the enter dataset and then do the query.
0
 

Author Comment

by:adamy12
ID: 11866446
Thanks for the help, but the find being used is the built in one (CTRL-F). The form is linked to a the table involved, but as I mentioned the same functionality was inifintely quicker than access 2003.
I haav added functionality to do a filter which is quick but everyone for some wierd reason likes the find function. Maybe because the filter returns a single record but after a find, you can move through all records in the recordset.
How would I change the find to a pass through query?  

Adam
0
 
LVL 3

Accepted Solution

by:
Catalys earned 250 total points
ID: 11874893
Depending on how you implemented your filter, you can achieve the same results...with more coding.

I would try these steps:
1 - Find which field is active when the Find/Filter button is clicked & save its name. This is the field that will be used for the filter.

2 - Clone the form recordset and use the FindFirst method.

This way you are not returning any subset, but simply pointing at the first record that matches the given criterion.

I would keep this method if it is faster than the other one.

HTH
0
 

Expert Comment

by:cstans
ID: 14067696
Was there any solution for this problem?
Every time i use the binocular (CTRL+F) to find a record in a table or in datasheet view in Access 2003 it takes forever. In access 2000 it was instantly.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question