Partial match in searching an Access DB 2

[FinnMan]

Ok Gurus:

I'm having a time of it trying to do a data return based on a partial match (i.e., searching for light will also return lightning, etc...)

I am "not" using ADO. Just straight DAO jet on an Access database. Should I be using a SQL query? So far I've been unlucky in finding any net references. Any examples or URL's would make my day

 

[jmerencilla]

i think it would be better for you if you use SQL query. it's easy, fast and flexible. also, instead of using DAO, try using ADO instead. i read somewhere that ADO is efficient than DAO. but if your comfortable with DAO, you may still use it.

have a nice day.

 

[dynamodc]

FinnMann

Try using this subroutine:-

Sub DAO_SQL_Search()

Dim db As Database
Dim rst As Recordset
Dim strSQL As String

' Open the database
Set db = DBEngine.OpenDatabase("c:\program files\microsoft office\office\samples\NorthWind.mdb&quot

'In the line below replace "sales" with a reference to the text box you want to search by (i.e. text1.text)
strSQL = "SELECT ContactName, ContactTitle FROM Customers WHERE ContactTitle like '" & "sales" & "*'"

' Open the Recordset
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

' Print the values
Do Until rst.EOF
MsgBox "Contact Name is: " & rst!ContactName & vbCr & "Contact Title is: " & rst!ContactTitle
rst.MoveNext
Loop

' Close the recordset
rst.Close

MsgBox "finished"
End ' terminate program

End Sub

Basically, what it does is search through the Customers table in Northwind and finds anyone with a job title that begins with sales. Obviously, you will need to customise this. Any problems let me know.

Good luck!

DC

 

[strongm]

ADO is generally more efficient than DAO - except in the case of Access/Jet, where DAO is still rumoured to be the faster of the two (I have to admit to not having done any timings to prove this).

 

[FinnMan]

Thank you all very much for your replies. I'll be putting them into practice this weekend

I've avoided ADO only because I'm not at all familiar with it. Guess it's time to crack open the books and learn something new ;-)