Solved

FIND function slow in Access 2003

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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