Simple Search ADO

[blondie:)]

I am trying to search an Access database using an ADO data control, and am having problems. I have the following code:

Dim NewSearch As String
NewSearch = InputBox("Enter User Login", "Search")
If NewSearch = ("") Then Exit Sub

adoLogin.RecordSource = "SELECT * FROM Logins WHERE Login LIKE '%" & NewSearch & " % '"

adoLogin.Refresh

It says I have an error in my FROM statement, and I can't see it at this point. I have tried several ways. Someone please help

[techgnome]

well, for starters, use * instead of %..... Access used the * wildcard, instead of the usual % in SQL.... also, it looks like there's a space before the last wildcard.... which may cause problems...

[stingrae]

techgnome is correct. also there should be another ' before the final * (assuming it is a string)

[blondie:)]

Okay, thanks for the replies. So I made the changes to the line as suggested and I have this, and still the same error:

Dim NewSearch As String
NewSearch = InputBox("Enter User Login", "Search")
If NewSearch = ("") Then Exit Sub
adoLogin.RecordSource = "SELECT * FROM Logins WHERE Login LIKE '*" & NewSearch & "'* '"
adoLogin.Refresh

It's all blurry to me at this point. Please help

[techgnome]

No, stingrae got it wrong.... there SHOULDN'T be ' before the *......
the * needs to be inside the quite identifiers ( that's the ' )
it should read like this:

adoLogin.RecordSource = "SELECT * FROM Logins WHERE Login LIKE '*" & NewSearch & "*'"

[stingrae]

oops. sorry, you're right. i meant after the *!

lack of coffee......

[blondie:)]

Okay, so now I have

Dim NewSearch As String
NewSearch = InputBox("Enter User Login", "Search")
If NewSearch = ("") Then Exit Sub
adoLogin.RecordSource = "SELECT * FROM Logins WHERE Login LIKE '*" & NewSearch & "*'"
adoLogin.Refresh


And still have the same error. Please help

[techgnome]

hmmmmm
I have to admit, I'm a bit stumped.... the only thing that comes to mind is if Login or Logins is a reserved word in Access.... try putting [] around Logins and Login and see if that helps....
adoLogin.RecordSource = "SELECT * FROM [Logins] WHERE [Login] LIKE '*" & NewSearch & "*'"

Something else I notices, Access is particular about having a ; at the end of SQL statements.....
adoLogin.RecordSource = "SELECT * FROM [Logins] WHERE [Login] LIKE '*" & NewSearch & "*';"

[stingrae]

one thing i often do to test my SQL, when i build it in VB and it fails, is to copy and paste it into Access.

i.e. open access, and create a new query, select SQL view and paste the completed SQL statement in there (normally i do a debug.print just before the rs.open and take that).

if it works in Access, then there's something wrong with the way the connection is setup, but if it fails in access, it will give you a better message.

[WizardLMB]

Try this one.

Depending on how many search criteria do you have, split the SQL code to be like this:-

Private Sub Search()
Dim myStr(1 to 3) as String
 
'Connect to a Database using code, not ADO.
Dim CNN as new ADODB.Connection
Dim CNNStr as String
Dim rstRecord as New ADODB.Recordset
 
CNNStr="Provider=Microsoft.Jet.OLEDB.4.0"; _
"Data Source= App.Path \Database.mdb"
 
CNN.Open CNNStr
 
myStr1 Like "*" & "txtText" & "*"
myStr2 Like "*" & "txtText" & "*"
myStr3 Like "*" & "txtText" & "*"
 
'Please check, if txtText is numeric, you might just have to add Like '"*" & "txtText" & "*"'
 
 
rstRecord.Open,"SELECT * FROM [Logins] WHERE myStr1 & myStr2 & myStr3, CNN     'Please verify my syntax code
 
Set DataGrid.DataSource=rstRecord
 
End Sub

I hope you come right

Wizard
SA

[blondie:)]

Thanks for the help, I finally got it