Solved

MS Access / SQL Search problem

Posted on 2001-08-07
6
178 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 50

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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