MS Access query with VB

Hi there!

I am trying to write a VB app in which the user should enter some search strings, which will search the database for matches, and the result will then be retuned to the user (via the app of course)

(ie. string=orange juice and it will return all the people that like the orange juice)

Can you please help me by giving me an example code of such a search query or maybe to suggest me site?

Thanks a lot

You have provided very little information. It would be easier to help you, if you posted database info like tablenames, field names, names of controls on your form, etc...

But to search a database for text a user entered on a form you could just run a sql query like the one below.

thasks, I am new to VB and i am now trying to find what is going on.

I have a database with a table called 'Customers' and 3 fields 'customer_name', 'customer_email', 'shop_id'

I want to build an app which will just show on the screen all the records (all three fields) that have the same 'shop_id' value.

In the application, this 'shop_id' value will be defined by the user.

i.e. An .exe with a 'search' button (which will search for all the matches for the field 'shop_id') and will display all the customers' name and customers' email that have the same shop_id.

I hope now it's more clear

Okay, so what exactly is the question?
If you are wanting to know how to query the database, just modify the sql statement above to something like this


I'm not really sure what you're asking though, it seems as if you want someone to create this application for you???

the code is this:

frmMain
Private Sub cmdSQL_Click()
Dim MsgText As String
'display in Grid
HflXRecords.Clear
Set HflXRecords.DataSource = _
ExecuteSQL(txtSQl, MsgText)
sbrStatus.Panels(1).Text = MsgText
End Sub

Private Sub HflXRecords_Click()

End Sub

Seperate module:

Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String

On Error GoTo ExecuteSQL_Error

sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = rst.RecordCount & _
" records found from SQL"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function

ExecuteSQL_Error:
MsgString = "ExecuteSQL Error: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function

Public Function ConnectString() _
As String
'returns a DB ConnectString
ConnectString = "Provider=" & _
"Microsoft.Jet.OLEDB." & _
"4.0;Data Source=" & _
App.Path & "\database.mdb"
End Function


And my question is:

Should I get out the IF ELSE statements and replace them with the code that u said?

Or maybe I should not use the module (except for the database connetion) and just use the frmMain with your code?

And if so, what where should I put it?