Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ado programming (vb)

    how can i open 2 ado recordsets in one form, and use the seek command for one of the recordsets. Appreciate if anyone can help me with this. tony

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ado programming (vb)

    You can open as many recordsets as you like, but what do you mean by "in one form"? If you want to *display* multiple recordsets, that's a different question.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ado programming (vb)

    thanks for replying,,,,,how can i open two ado recordsets in one form? i have done it with dao but confused with ado,,,please send me s short example........thanks again,,,tony

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ado programming (vb)

    Please tell us what you want to accomplish. Perhaps if you post the ADO code you have now, or the DAO code you used to have, we will be able to help you better. As Charlotte wrote, opening two recordsets is easy in itself:

    Dim cnn As New ADODB.Connection
    Dim rst1 As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:AccessTest.mdb"
    rst1.Open "tblOne", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst2.Open "SELECT Field1, Field2 FROM tblTwo Where Field3 > 100", cnn, adOpenForwardOnly, adCmdText

    This opens two recordsets for further manipulation in code; it doesn't display them on the form.

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ado programming (vb)

    THANKS FOR YOUR HELP, THE CODE WAS SOMETHING I NEEDED. I simply want to use two ado recordset in one form and with the employee-id in one recordset,,,,seek the employee-id in the second recordset and bring back the personal data for the employee, and at the end display that information on the form. Can this be done? Again grateful for your help! tony

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ado programming (vb)

    Is this in VB6? I don't have that available at the moment, so somebody else will have to take over.

    (In Microsoft Access, this would probably be easier)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ado programming (vb)

    I'm baffled by why you want to use two recordsets if both contain the same key. Why not just do a seek on a single recordset?
    Charlotte

  8. #8
    New Lounger
    Join Date
    Oct 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ado programming (vb)

    charlotte thanks for all the help i have received and it has been very helpful. Let me see if i can explain,,,,in one recordset the transactions of employees are recorded, but only with employee id or number, and the other recordset has the personal data of the employee. For reports and displaying purposes, I need to access the personal data recordset with a seek instruction, and my procedure says the provider jet engine 3.51 does not provide this indexing. Maybe there is a better and easier way to do this, if so , please advise, again thanks to all the staff and this is truly a great website. Tony

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ado programming (vb)

    Can you use the Find method instead of Seek?

    Dim rst As New ADODB.Recordset
    Dim lngEmployeeID As Long

    lngEmployeeID = ...
    rst.Open ...

    rst.Find "EmployeeID = " & lngEmployeeID

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ado programming (vb)

    I see. It wasn't clear that the recordsets contained different data and you wanted to display *additional* data from the second recordset. Is there a reason you're using the Jet 3.51 provider? The code Hans posted uses the Jet 4.0 provider, which is a definite improvement over the 3.51 version. Is your database in Access 97?
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ado programming (vb)

    Depending on how you are obtaining these recordsets, I would suggest a more proper method, which is to create a single
    recordset instead, by joining the two tables, shuch as:
    <pre>Select t.TranDate, t.TranAmount, e.EmployeeID, e.EmployeeName _
    From Transactions AS t Inner Join Employees AS e on t.EmployeeID = e.EmployeeID _
    Where .....(put appropriate where conditions here) </pre>


    You can then refer to fields in the recordset without having to seek from a second one.

  12. #12
    New Lounger
    Join Date
    Oct 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Short Thank You

    I am new to this site, and was truly impressed with all the help received on my inquiry, just wanted to thanks all the Staff for the kindness shown, and this is really a great site where questions are fully answered. I am grateful, Tony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •