Search Access Database 1

[NTesla1886]

I have written a program in VB 6. The user has the option to select from six different fields to search from using option buttons. Their is a text box for the user to enter the text to search for. The user cannot search by multiple fields, just one that is the reason for option buttons not check boxes

The search should display all results that matches the entered text.

I have worked with VB and Access before, I have performed searches before. I have never performed a search of this magnitude.

I have not idea what I am doing or how to proceed.

Any assistance would greatly be appreciated

 

[Swi]

You will want to look into ADO for doing this. Take a look a this FAQ:

faq222-3735

Try using this as a basis and post back when you get stuck on something. I would recommend either displaying the results of the recordset on the form you have if the information you have is simple or in VB's interal datareport if the information needs to be a bit more detailed.

Swi

 

[Swi]

By the way, welcome to Tek-Tips.

Swi

 

[NTesla1886]

I can open the data base and recordset. I am also able to enter, update, and remove records.

The only problem I have is creating the search code.

 

[ArtieChoke]

What part of this is hanging you up - how to structure the search code for Access or how to go thru the text boxes to create the search criteria or ???

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Swi]

Take a look at the FAQ I posted. Also, have a look at this link.

http://www.w3schools.com/sql/sql_select.asp

Swi

 

[NTesla1886]

I need assistance with the code that actually performs the search, then once the search is completed how to take the results and display them. They will either be put into an Excel worksheet or Word depending on what the user wants.

The Word and Excel I can handle.

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[NTesla1886]

I think I have the Select Statement down now. I understand that I need the IIF statement but I do not know how to use it or the purpose. I have done a google search but everything I find does not do a good job of explaining its use or purpose etc.

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[JoeAtWork]

The first link that Swi gave you will get you as far as loading the data in to a recordset, all you have to do is change the SELECT statement according to your search parameters. Since you have stated you have done searches before, I trust you already know how to compose the SQL.

With the recordset open, you just need to loop through it using the navigation methods (hint: MoveNext), and you can output the field values to Word or Excel. Doing a Google search on "ADO recordsets" should give you all the details you need. If you are still stuck, post the code you have so far and most likely someone will help you out.

 

[JoeAtWork]

I don't see why you need an IIF statement, from what you describe your SQL would be using a LIKE operator, e.g. something like:

SELECT Field1, Field2 FROM Table1 WHERE Field1 LIKE "%SearchWord%"

Note that when using ADO that % is used instead of *, and _ is used instead of ?

 

[NTesla1886]

My statement of performing searches may be premature. I have not used SGL statements before. It was more like using somthing like this. It may have been un-orthodox or inproper but it worked for what I needed

If text1.text = Recordset!fieldname then

Else

End if

That method worked for what I was doing at the time. I was only searching one field. Now that I am search multifields that method will not work.

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[Swi]

Post the code that you are having trouble with and we will work with you to get it resolved.

Swi

 

[NTesla1886]

I am not 100% sure how but I got it working. It is giving me the output I am expecting, so far.

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[BobRodes]

Kevin, perhaps you will still post your code. Like any form of construction, there are ways to "get it working" that do not hold up well over time, especially if you're not quite sure how you got it working.

In particluar, you ought to look at the Find and the Filter methods if you haven't already. I suspect you may have been using Find, and come up against its inability to handle multiple fields easily. Filter suppresses all records in a recordset that do not meet given criteria, which criteria may be anything valid in an SQL WHERE clause.

So, what are you doing now that works?

Bob

 

[TheCloak]

Hey Tektilityva I have gone through what you are talking about.

If you need any help I have a search access DB that searches based of of 20 or more fields all coexisting. If you have info in each box (all 20 at once) it can sum the info down to that.

Just let me know send me a message or something and I can try to help.

Sorry I was late getting on here.

--
-TheCloak

"You Never Know What Hits You, A Gunshot is the Perfect Way" - JFK

 

[NTesla1886]

So far this is the code that I have. It seems to be working right now on my machine. I have not tested it on any others as of yet.

If their is a better way or a why that will be faster with larger DB's then please let me know

As I said before I have done searches before but not like this so I do not fully understand what everything does.

Dim STRSQL As String
Dim MDC As adodb.Connection
Dim MRS As Recordset

STRSQL = "Select * From Customers"

MDC.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & MSTRDB & ";mode=readwrite"

MDC.Open
MRS.CursorLocation = adUseClient
MRS.Open STRSQL & IIf(MSTRSearch = "", "", " Where " & MSTRSearch & " = '" & Trim(TXTSearch.Text) & "'"), MDC
MRS.MoveFirst

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[TheCloak]

that seems like the right path to go down for your example. I have another way that I did mine.

I'm currently out of town so I don't have an example I will be able to post my sample database when I get back to the office.

I base my search criteria on the text boxes or combo boxes i use and then search criteria in a subform based off of the text within those fields.

It works really fast over 950 records and the results are summed down within milliseconds.

--
-TheCloak

"You Never Know What Hits You, A Gunshot is the Perfect Way" - JFK

 

[BobRodes]

<subform

That's fine if you're using Access, but there isn't direct support for one in VB6.

Tek, your idea looks workable. You might want to ask yourself whether you think it's better to make a trip to the database each time you need to make a search, or to have your customer table locally and do the search on the local copy. Each has its strenghs and drawbacks. Presently, you're making a trip each time; the way to keep a local copy of the table is to pull all the customer records into a recordset and use the .Filter property to restrict the view to those records matching the search criteria. This approach is faster but can have concurrency problems, meaning that any changes to the underlying database won't be reflected in the recordset until you reopen it. If you're always adding new customers you'll need to keep refreshing the recordset if you want to see them.

Finally, you could also handle multiple fields with your approach (or with the .Filter approach). A basic idea would be to populate a two dimensional array with each user-selected field and search value, then loop through the array, adding to your search string as you go. (I wouldn't use two string variables in my search string if I were you, one is fine.) If you need help with this, post back.

 

[NTesla1886]

Yes I am using Access.

For now I am satisfied with how it is running. In a future version I will address speed of the searc.

Also at this point the ability to search multiple fields is not necessary

Kevin
Tektility Inc

http://www.tektility.com

Maximizing Potential Through Technology

 

[BobRodes]

<Yes I am using Access.

In that case, you might prefer to use the Access forum for any further questions.