DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Searching for records contained in access from VB

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Jan 2005

Posts: 5

Reputation: lostprophet is an unknown quantity at this point

Solved Threads: 0

lostprophet lostprophet is offline

Offline

Newbie Poster

Searching for records contained in access from VB

Feb 22nd, 2005

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 :-)

•

Join Date: Feb 2005

Posts: 199

Reputation: Tight_Coder_Ex is an unknown quantity at this point

Solved Threads: 14

Tight_Coder_Ex Tight_Coder_Ex is offline

Offline

Junior Poster

Re: Searching for records contained in access from VB

Feb 22nd, 2005

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim Sql as String
 
Sql = "SELECT * FROM Table WHERE Str = '" & txtInputBox & "'"
Set Rs = Db.OpenRecordset (Sql)
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.

•

Join Date: Feb 2005

Posts: 238

Reputation: uniquestar is an unknown quantity at this point

Solved Threads: 11

uniquestar uniquestar is offline

Offline

Daniweb Sponsor

Re: Searching for records contained in access from VB

Feb 28th, 2005

•

Originally Posted by 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
Help with Code Tags

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 Dim Sql as String
 
 Sql = "SELECT * FROM Table WHERE Str = '" & txtInputBox & "'"
 Set Rs = Db.OpenRecordset (Sql)
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?

•

Join Date: Feb 2005

Posts: 199

Reputation: Tight_Coder_Ex is an unknown quantity at this point

Solved Threads: 14

Tight_Coder_Ex Tight_Coder_Ex is offline

Offline

Junior Poster

Re: Searching for records contained in access from VB

Feb 28th, 2005

•

Originally Posted by uniquestar

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

•

Join Date: Jul 2004

Posts: 200

Reputation: mnemtsas is an unknown quantity at this point

Solved Threads: 1

mnemtsas

Offline

Junior Poster

Re: Searching for records contained in access from VB

Mar 11th, 2005

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

Mark Nemtsas

Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books

•

Join Date: Apr 2005

Posts: 8

Reputation: Mohsin Khan is an unknown quantity at this point

Solved Threads: 0

Mohsin Khan Mohsin Khan is offline

Offline

Newbie Poster

Re: Searching for records contained in access from VB

Apr 12th, 2005

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"

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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

•

Join Date: Mar 2009

Posts: 10

Reputation: KSS is an unknown quantity at this point

Solved Threads: 1

KSS

Offline

Newbie Poster

Re: Searching for records contained in access from VB

Mar 29th, 2009

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...

•

Join Date: Feb 2005

Posts: 35

Reputation: koolsid is an unknown quantity at this point

Solved Threads: 6

koolsid

Offline

Light Poster

Re: Searching for records contained in access from VB

Mar 29th, 2009

@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

A good excercise for the Heart is to bend down and help another up...

Please Mark your Thread "Solved", if the query is solved...

==>If a post has helped you then Please Rate it!