Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 14728

Feb 22nd, 2005

Searching for records contained in access from VB

Expand Post »

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

Similar Threads

how many records can Access table hold? in Visual Basic 4 / 5 / 6

lostprophet

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jan 2005

Permalink

Feb 22nd, 2005

Re: Searching for records contained in access from VB

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

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.

Tight_Coder_Ex

Reputation Points: 47

Solved Threads: 17

Posting Whiz in Training

Offline

215 posts
since Feb 2005

Permalink

Feb 28th, 2005

Re: Searching for records contained in access from VB

Quote 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
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?

uniquestar

Reputation Points: 49

Solved Threads: 11

Posting Whiz in Training

Offline

239 posts
since Feb 2005

Permalink

Feb 28th, 2005

Re: Searching for records contained in access from VB

Quote 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

Tight_Coder_Ex

Reputation Points: 47

Solved Threads: 17

Posting Whiz in Training

Offline

215 posts
since Feb 2005

Permalink

Mar 11th, 2005

Re: Searching for records contained in access from VB

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

mnemtsas

Reputation Points: 16

Solved Threads: 1

Posting Whiz in Training

Offline

200 posts
since Jul 2004

Permalink

Apr 12th, 2005

Re: Searching for records contained in access from VB

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"

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

Mohsin Khan

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Apr 2005

Permalink

Mar 29th, 2009

Re: Searching for records contained in access from VB

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

KSS

Reputation Points: 11

Solved Threads: 3

Newbie Poster

Offline

21 posts
since Mar 2009

Permalink

Mar 29th, 2009

Re: Searching for records contained in access from VB

@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

koolsid

Reputation Points: 11

Solved Threads: 6

Light Poster

Offline

35 posts
since Feb 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Apply code to 'x' button on form

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Copy all folder contents to specified location(pls help)

DaniWeb Message

Cancel Changes

User Name
Password