?
Solved

MS Access / SQL Search problem

Posted on 2001-08-07
6
Medium Priority
?
190 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:K-9
6 Comments
 

Author Comment

by:K-9
ID: 6361609
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.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6361649
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
rs.Close
Set rs = Nothing

end sub

does this make any sense?

dovholuk
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 6363053
Hi K-9,

You can use the Filter function in Access:

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

regards.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:K-9
ID: 6374552
Dov.. Can you clarify more?  So far my only option is to make the fields I wanna search, indexed.  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6996567
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7014275
Per recommendation, points refunded and question closed by
Netminder
CS Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.
Suggested Courses

850 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