Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Do pass thru query connections persist?

    I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

    I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.

    BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
    http://msdn.microsoft.com/library/de...tml/sa01l8.asp
    The bottom entry (Assigning recordsets to controls) is one I like.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.

    I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?

    Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?

    Pootle thanks again for the article.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gwgeller
    Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?
    The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.

    Here is my code behind the report.

    Code:
    Private Sub Report_Open(Cancel As Integer)
        
        Dim conGlob As New ADODB.Connection
        Dim rst As New ADODB.Recordset
    
        conGlob.ConnectionString = "Provider=SQLOLEDB;" _
                                    & "Data Source=SERVER;" _
                                    & "Initial Catalog=DatabaseTable;" _
                                    & "Trusted_Connection=Yes;"
                                    
        conGlob.CursorLocation = adUseClient
    
        conGlob.Open
        
        With rst
            .ActiveConnection = conGlob
            .CursorType = adOpenStatic
            .CursorLocation = adUseClient
            .LockType = adLockReadOnly
        End With
    
        rst.Open "usp_GetStates", , , , adCmdStoredProc
    
        Set Me.Recordset = rst
    
        rst.Close
        conGlob.Close
        
    End Sub
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your best bet would be to use an access data project and use stored procedures

    First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections

    1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.
    Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Posts
    145
    I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.

    If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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