MS Access / SQL Search problem

I created a simple find button on the access form.  And I have a SQL server back end.  Everything works well, adding, deleting records, etc..   Except when I use the "find" button"  Sometimes it will work when I have the criteria set at "Whole field" and know there is a matching data in that field.  Any other option such as "partial field" or if I search for a record that doesnt exist, it will just hang there.  I can hit ctrl+break to break the search, but type ctrl+alt+delete brings up the task manager, saying MS Access is not responding.  Anyone have any experience with this?

I get this hang-up sometimes as well even w/ its set as "whole field" & using data I know would match.  Is there something I need to do at the sql server?  This was a Access DB, and used the upgrade wizard for SQL.  Im using Access 2000.
Who is Participating?
NetminderConnect With a Mentor Commented:
Per recommendation, points refunded and question closed by
CS Moderator
K-9Author Commented:
Update:  Seems like the searches work great if they are done in a field that's indexed.  I have like 50 forms & anywhere from 5 to 50 different fields for each of my 100 tables.  

Do I need to index every field I want to search on?  I need some advice.
the problem with using access and the find feature is that access will pull the whole table to the clent pc row by row comparing each row until it finds a hit.  this is not acceptable for tables which are evenly slightly long (10000+ rows). i think what you should investigate is creating your own find routine.

the way i'd attack it (i'm assuming your forms are all bound to tables) is with a DAO.Recordset and a Me.RecordsetClone...  here's some sample code. it requires a reference to DAO (post here if you need help with the reference). put this code behind a button on your form called "cmdSearch" and replace the item in curly brackets {} with appropriate information...

Sub cmdSearch_Click()

    dim rs as DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "{Field in the table your form is based on} = 'sometextvalue'"
Me.Bookmark = rs.Bookmark
Set rs = Nothing

end sub

does this make any sense?

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Ryan ChongCommented:
Hi K-9,

You can use the Filter function in Access:

Form.Filter = "[Field1] like '*" & serchvalue & "*'"
Form.FilterOn = True

K-9Author Commented:
Dov.. Can you clarify more?  So far my only option is to make the fields I wanna search, indexed.  
for K-9

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.