Results 1 to 10 of 10
  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
    First, I get an error saying "invalid use of property", highlighting the connection word in this line:
    rsTest1.Connection = cnn2

    Daniel
    Last edited by kedaniel; 08-02-04 at 15:09.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm confused.. why do you need two identicle connections?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Quote Originally Posted by Teddy
    I'm confused.. why do you need two identicle connections?
    Hey, I am the confused one here! ;-)

    It might not be necessary for all I know, perhaps I have misunderstood the purpose of sharing one connection object?

    In order to use two or more recordsets at the same time, calling and using them within the same form using DAO, do I need to establish a connection, and explicitly tell which rs should use the connection when needed?

    - or could I simply skip using the connection object, just open/close several rsets at will without thinking about "connection"? What is needed?

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

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The same connection can support multiple recordsets. You don't need a distinct connection for each one.... Is that what you are asking?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Quote Originally Posted by Teddy
    The same connection can support multiple recordsets. You don't need a distinct connection for each one.... Is that what you are asking?
    hm. Maybe I do not need to explicitly SWITCH between them? I was under the impression (from some ADO code samples I think(?)) that one have to point the connection to rs1 , then point to rs2 when using that,by stating:
    rsTest1.Connection = cnn2
    then when using Test2 on that connection, :
    rsTest2.Connection = cnn2

    If this is not necessary, I must have misunderstood just what that piece of code does. If that is the case, could you tell me why/when one need to us that:

    rsX.Connection = cnn

    Is it only done once for EACH recordset at the beginning, then not more further when using it? I guess it must be set for each rs?

    Thanks so much for the quick replies today

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

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are using ADO then you need a connection. But if you are using DAO then you need to open a database. There is no Connection object in the DAO object model. In DAO use CurrentDB or DBEngine.OpenDatabase to open the database the table is in. Then each recordset can use the same database object to create the recordset.

  7. #7
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    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
    What about these lines used with ADO:
    ' Set cnn = New DAO.Connection
    ' Set cnn.ConnectionString = CurrentProject.Connection

    If I need to do something like that in DAO, what is the correct syntax?

    The connectionstring can point to a external db/network, a local mdb, etc.
    But: what is the CurrentProject.connection? Is it "establlished" somewhere? can it be "redirected"? Is it needed? Is it assumed as default if not specified? I think this sample says that the cnn object should connect through "what is already being used"?

    I don't get all this, mainly want to know what is the minimum safe code when using multiple recordsets that only work against MDB linked tables or local MDB tables.

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

  8. #8
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Quote Originally Posted by DCKunkle
    If you are using ADO then you need a connection. But if you are using DAO then you need to open a database. There is no Connection object in the DAO object model. In DAO use CurrentDB or DBEngine.OpenDatabase to open the database the table is in. Then each recordset can use the same database object to create the recordset.
    So is this all that is needed?: (no need for cnn or other?)

    Code:
        Dim db As DAO.Database
        Set db = CurrentDb()
        Dim rsTest1 As DAO.Recordset
        Dim rsTest2 As DAO.Recordset
    Any other optional(?) DAO "settings" that are important to know about, or will this suffice? (I know one: the Workspace, which can be used for example with different permissions than the current user, seems very useful in certain situations.)

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

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    That is pretty much it. But if you wanted to get to a table from Db A that is in Db B then you will need:

    DBEngine(0).OpenDatabase

    You can then specify a path and filename for the database. With one database object you can open many recordsets, but just keep in mind that they all go away when you close the database object.

  10. #10
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Smile

    Quote Originally Posted by DCKunkle
    just keep in mind that they all go away when you close the database object.
    Yes, could be important to group in more objects, obviously.
    Thanks!

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

Posting Permissions

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