Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: Using multiple recordsets with one DAO connection?

    could someone please post a sample where they switch between two DAO recordsets (mdb tables) on the same connection ?

    I am doing something wrong here, and cannot seem to find samples on the net.

    I have not used ADO much, and there is something I do not understand about the usage of multiple connections. It was easy to find ADO code examples, but I cannot figure out what I need to change for it to work with DAO.

    My code:
    Code:
    Testing:
        Dim cnn2 As DAO.Connection
    
    'copied from ADO, not working
    '    Set cnn = New DAO.Connection 
    '    Set cnn.ConnectionString = CurrentProject.Connection
    
        Dim db As DAO.Database
        Dim rsTest1 As DAO.Recordset
        Dim rsTest2 As DAO.Recordset
        Set db = CurrentDb()
        
        Dim strsqlX As String
        strsqlX = "(select * from tbl_X)"
        
        Dim strsqlY As String
        strsqlY = "(select * from tbl_Y)"
        
    SharingTheConnection:
        rsTest1.Connection = cnn2
        Set rsTest1 = db.OpenRecordset(strsqlX, dbOpenSnapshot)
        Debug.Print rsTest1.GetRows
        
    Shift2:
        rsTest2.Connection = cnn2
        Set rsTest2 = db.OpenRecordset(strsqlY, dbOpenSnapshot)
        Debug.Print rsTest2.GetRows
    
    Shift1:
        rsTest1.Connection = cnn2
        Debug.Print rsTest1.GetRows
        
    IfNot:
        '(do I need to SET the recordset again when switching? Do not think so...)
        rsTest1.Connection = cnn2
        Set rsTest1 = db.OpenRecordset(strsqlY, dbOpenSnapshot)
        Debug.Print rsTest1.GetRows
    
    Stop
    Daniel
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    You have an mixture of ado and dao going on which is probably what is messing you up.

    'copied from ADO, not working
    ' Set cnn = New DAO.Connection
    ' Set cnn.ConnectionString = CurrentProject.Connection
    Keep everything in ADO or DAO. This is one possible way

    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim SQL As String
    Set cn = CurrentProject.Connection
    Set rs1 = New Recordset
    Set rs2 = New Recordset

    Then you could open the recordsets as follows
    SQL = "Select * from table1"
    rs1.Open SQL, cn

    SQL = "Select * from table2"
    rs2.Open SQL, cn

    You could then use these recordsets as needed and close them when you are done with them
    rs1.close
    rs2.close


    Similar idea if you prefer to stick with dao
    Hope that helps.
    steve.

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Yes,have been using the ADO, and my question was about its equivalents in DAO, as I will use DAO for some things. Getting to like DAO better, in fact.

    Daniel
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Here is some code you can use which uses one connection with multiple recordsets. Replace the variables with your own criteria.

    Code:
    Private wsSCIROCCO As DAO.Workspace
    Private dbSCIROCCO As DAO.Database
    Private rsSCIROCCO_One As DAO.Recordset
    Private rsSCIROCCO_Two As DAO.Recordset
    
    Set wsSCIROCCO = CreateWorkspace("", "admin", "", WorkspaceType)
    Set dbSCIROCCO = wsSCIROCCO.OpenDatabase(strDBName, DatabaseExclusive, DatabaseReadOnly, strODBCConnectionString)
    
    Set rsSCIROCCO_One = dbSCIROCCO.OpenRecordset(strSQL, RecordsetType, RecordsetOptions, LockType)
    
    Set rsSCIROCCO_Two = dbSCIROCCO.OpenRecordset(strSQL, RecordsetType, RecordsetOptions, LockType)
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

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