Solved

MS Access / SQL Search problem

Posted on 2001-08-07
6
176 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 49

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now