Searching for records contained in access from VB

[lostprophet]

Hi there,
i m not a very advanced programmer so i have now come up against a brick wall. i need to use a VB code preferably on a command button, whereby the user enters a keyword into a text box, clicks the 'Search' command button, and then a procedure looks up the information from the tables contained in the relational access database and displays it in text boxes on a VB form.
I am using the ADODC VB function.

Any help will be appreciated i am DESPERATE!! thanx alot :-)

[Tight_Coder_Ex]

I'm not sure how similar ADODC is to DAO, but this is what I've used in the command buttons click event

Dim Sql as String
 
Sql = "SELECT * FROM Table WHERE Str = '" & txtInputBox & "'"
Set Rs = Db.OpenRecordset (Sql)

Table, txtInputBox are my substitutions, I think you'll understand how you apply to your table and text box.

This it's just a matter of cycling through the records returned by Rs and populate whatever text, list or combobox or whatever.

[uniquestar]

I'm not sure how similar ADODC is to DAO, but this is what I've used in the command buttons click event

Dim Sql as String
  
 Sql = "SELECT * FROM Table WHERE Str = '" & txtInputBox & "'"
 Set Rs = Db.OpenRecordset (Sql)

Table, txtInputBox are my substitutions, I think you'll understand how you apply to your table and text box. This it's just a matter of cycling through the records returned by Rs and populate whatever text, list or combobox or whatever.

Hi, I just tried using this code, but got a whole load of run-time errors, doesn't seem to work with ADODC
do you have anything else?

[Tight_Coder_Ex]

doesn't seem to work with ADODC do you have anything else?

Sorry, 90% of my work is done in assembly and the other in C++. DAO is the only thing I've ever used with VB

[mnemtsas]

Try taking a look at my ADO tutorial http://www.timesheetsmts.com/adotutorial.htm

[Mohsin Khan]

This is a very simple question, and I don't know y everyone tries to teach lostprophet in his manner.
You just right this code and assume that ur database name is "test.mdb".And u have table named "t1" having 2 fields "invoice" & "Name"

Private Sub Command1_Click()
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.mdb;Persist Security Info=False"
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = "select name from t1 where invoice like '" & Textbox1.Text & "'"
    Adodc1.Refresh
If Adodc1.Recordset.RecordCount > 0 Then
   Textbox1.Text=Adodc1.Recordset.Fields(0)
End if 
End Sub

[KSS]

My suggestion is to use DAO 3.6 Library..it's easier to use than ADO..
Your solution would be like:
------------------------------------------------------
Private Sub CmdSearch_Click()
Dim ws as Dao.Workspaces
Dim db as Dao.database
Dim rs As RecordSet
Dim mysql As string
Dim search as String

On Error Resume Next
set ws = dbengine.workspaces
set db = ws(0).openDatabase(app.path & "\test.mdb".False.False)

search = Inputbox("Enter something to search: ")
mysql = "SELECT * FROM table_name WHERE field_u_want like '*" & search & "*'"
Set rs = db.OpenRecordSet(mysql)
If rs.recordcount = 0 Then Msgbox("No Results.")

rs.Close
db.Close
ws(0).Close

Set rs = Nothing
Set ws = Nothing
Set db = Nothing

If err.Number <> 0 then Msgbox err.description
End Sub
------------------------------------------------------
I hope this will help you...

[koolsid]

@KSS: That thread is 5 years old :)

The original poster must have already got the answer by now...

Also I will never recommend DAO. ADO is much faster ;)