Solved

FIND function slow in Access 2003

Posted on 2004-08-21
7
332 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
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now