Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access / SQL Search problem

Posted on 2001-08-07
6
Medium Priority
?
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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