Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

VB using ADODB.Connection to MS Access database

slickrickslickrick Posts: 1Member
Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.

I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?

I want to seearh the field surname, how would I do this.

All help would be appreciated

Comments

  • Termc1224Termc1224 Posts: 84Member
    : Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.
    :
    : I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?
    :
    : I want to seearh the field surname, how would I do this.
    :
    : All help would be appreciated
    :
    Try this sample
    Place 1 command button for search
    place 4 textbox
    4 labels

    set labels thats up to you
    set textbox
    text1.datafield = Surname
    text2.datafield = Firstname
    text3.datafield = MI
    (text4) for inputing the search entry

    in the general declaration of your code enter this
    dim conn as new adodb.connection
    dim rs as new adodb.recordset

    in the form_load event
    conn.cursorlocation = Aduseclient
    conn.open your mdb's path
    rs.open "select * from " the table " order by surname", conn, _ adOpenDynamic, adLockOptimistic
    text1.datasource = rs.datasource
    text2.datasource = rs.datasource
    text3.datasource = rs.datasource

    In the Command(Search)Button Click Event
    rs.movefirst
    rs.find "surname like '" & text4.text & "'"
    if rs.eof then
    rs.movefirst
    msgbox("No Record Found")
    end if

    hope this help
  • BarkeeperBarkeeper Posts: 335Member
    : : Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.
    : :
    : : I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?
    : :
    : : I want to seearh the field surname, how would I do this.
    : :
    : : All help would be appreciated
    : :
    : Try this sample
    : Place 1 command button for search
    : place 4 textbox
    : 4 labels
    :
    : set labels thats up to you
    : set textbox
    : text1.datafield = Surname
    : text2.datafield = Firstname
    : text3.datafield = MI
    : (text4) for inputing the search entry
    :
    : in the general declaration of your code enter this
    : dim conn as new adodb.connection
    : dim rs as new adodb.recordset
    :
    : in the form_load event
    : conn.cursorlocation = Aduseclient
    : conn.open your mdb's path
    : rs.open "select * from " the table " order by surname", conn, _ adOpenDynamic, adLockOptimistic
    : text1.datasource = rs.datasource
    : text2.datasource = rs.datasource
    : text3.datasource = rs.datasource
    :
    : In the Command(Search)Button Click Event
    : [red]rs.movefirst
    : rs.find "surname like '" & text4.text & "'"
    : if rs.eof then
    : rs.movefirst
    : msgbox("No Record Found")[/red]
    : end if
    :
    : hope this help
    :

    And if the found surname is in the last record you'll still get your "No Record Found"-Message which would be in this case wrong. I don't know ADO (I'm using direct ODBC-API-Calls), but i do remember from DAO that there was something like a NoMatch-Property. Is there something similiar in ADO?
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • lionblionb Posts: 1,685Member
    [b][red]This message was edited by lionb at 2006-4-25 9:13:27[/red][/b][hr]
    [b][red]This message was edited by lionb at 2006-4-25 9:9:21[/red][/b][hr]
    : : : Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.
    : : :
    : : : I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?
    : : :
    : : : I want to seearh the field surname, how would I do this.
    : : :
    : : : All help would be appreciated
    : : :
    : : Try this sample
    : : Place 1 command button for search
    : : place 4 textbox
    : : 4 labels
    : : in the form_load event
    : : conn.cursorlocation = Aduseclient

    This is more efficient way to do search on any database is using WHERE clause

    In the Command(Search)Button Click Event
    [code]
    rs.open "select * from the table [blue]where[/blue] surname = '" & Text4.Text & "' order by surname", conn, _ adOpenDynamic, adLockOptimistic
    if rs.EOF then
    Msgbox "Record not found " & Text4.Text
    Else
    Do until rs.EOF
    text1 = rs.Fields("Surname").Value
    text2 = rs.Fields("FirstName").Value
    text3 = rs.Fields("MI").Value
    rs.MoveNext
    Loop
    end if
    [/code]
    Note, if you have multiple records for surname - like Smith - you have to write code to exit loop or use listview or other grid contrlos to display data.




  • Termc1224Termc1224 Posts: 84Member
    : [b][red]This message was edited by lionb at 2006-4-25 9:13:27[/red][/b][hr]
    : [b][red]This message was edited by lionb at 2006-4-25 9:9:21[/red][/b][hr]
    : : : : Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.
    : : : :
    : : : : I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?
    : : : :
    : : : : I want to seearh the field surname, how would I do this.
    : : : :
    : : : : All help would be appreciated
    : : : :
    : : : Try this sample
    : : : Place 1 command button for search
    : : : place 4 textbox
    : : : 4 labels
    : : : in the form_load event
    : : : conn.cursorlocation = Aduseclient
    :
    : This is more efficient way to do search on any database is using WHERE clause
    :
    : In the Command(Search)Button Click Event
    : [code]
    : rs.open "select * from the table [blue]where[/blue] surname = '" & Text4.Text & "' order by surname", conn, _ adOpenDynamic, adLockOptimistic
    : if rs.EOF then
    : Msgbox "Record not found " & Text4.Text
    : Else
    : Do until rs.EOF
    : text1 = rs.Fields("Surname").Value
    : text2 = rs.Fields("FirstName").Value
    : text3 = rs.Fields("MI").Value
    : rs.MoveNext
    : Loop
    : end if
    : [/code]
    : Note, if you have multiple records for surname - like Smith - you have to write code to exit loop or use listview or other grid contrlos to display data.
    :
    :
    :
    :
    :What you just gave him is filtering not searching.
    this will remove other data in recordset, use find to search and sort your lastname by using order by in the main query. this will give you all the smith in example searching for smith.

  • lionblionb Posts: 1,685Member
    : :
    : :What you just gave him is filtering not searching.
    : this will remove other data in recordset, use find to search and sort your lastname by using order by in the main query. this will give you all the smith in example searching for smith.
    :
    For informatin recordset Find method
    [code]
    rs.find "surname like '" & text4.text & "'"
    [/code]
    does exactly the same thing that SQL SELECT statement
    [code]
    Select Surname From Table Where Surname like '" & text4.text & "'"
    [/code] does.
    If LastName is Smith and there 20 records for Smith, it will return all 20 records where LastName is Smith. At the same time, using recordset method to look for particular record is quite inefficient.


  • Termc1224Termc1224 Posts: 84Member
    : : :
    : : :What you just gave him is filtering not searching.
    : : this will remove other data in recordset, use find to search and sort your lastname by using order by in the main query. this will give you all the smith in example searching for smith.
    : :
    : For informatin recordset Find method
    : [code]
    : rs.find "surname like '" & text4.text & "'"
    : [/code]
    : does exactly the same thing that SQL SELECT statement
    : [code]
    : Select Surname From Table Where Surname like '" & text4.text & "'"
    : [/code] does.
    : If LastName is Smith and there 20 records for Smith, it will return all 20 records where LastName is Smith. At the same time, using recordset method to look for particular record is quite inefficient.
    :
    :
    : No their not exactly the same, select is filtering, not searching. rs.find will point you to the first smith in the example of 20 smith, and by using rs.movenext you'll move to the next smith. while select will return only 20 smith records, for example you are currently on the 20th of smith records. this will not show other record that does not contain smith as its lastname. rs.find is searching, select is filtering and will only 20 records in your example.

  • donjiedonjie Posts: 2Member
    : Hi there I am writing a program in visual basic with the ADODB with a database built in Microsoft Access.
    :
    : I want to know how to make searches with the data stored in the database. I heard it was something to do with SQL?
    :
    : I want to seearh the field surname, how would I do this.
    :
    : All help would be appreciated
    :

Sign In or Register to comment.