Server connection - how long is it held

I am working with getting data from a server into an ACCESS 2003 database.  Originally, I had about 12 procedures - each created a connection, made the call to the server, downloaded the data locally and then closed the connection.  I had a suggestion to use one connection and then change the SQL statement.  I did this and it does work.  that is, I have the connection at the top of the proceudre, and then as it processes, the SQL statement is simply changed.  However, this procedure can take up to 30 minutes to complete - is the same connection held for each SQL staetment changed and I am connected for the 30 minutes or does it use the connection, but re-connect with each SQL statement change?  I need to know becuase if I hold a connection for 30 minutes, - I am going to get a nasty phone call from the IT department!  Attached is partial code of the procedure.  I need to figure out the best way to get all the data without holding a connection for any serious length of time.
Public Sub proc_qPT_t_distr()
On Error GoTo ErrorHandler
Dim strReportDate As String
strReportDate = CStr(Format(DateSerial(Year(Date), (Month(Date) - 18), 0), "m/d/yyyy"))

If LinkDatabases("Link") Then Exit Sub
    If DoesObjectExist("qPT", "Query") Then DoCmd.DeleteObject acQuery, "qPT"

Set qdfSelect = CurrentDb.CreateQueryDef("qPT")
qdfSelect.Connect = SetConn("CRS")
qdfSelect.ODBCTimeout = 180

qdfSelect.SQL = "SELECT * FROM crs_db.dbo.t_distr "
DoCmd.RunSQL "DELETE * FROM t_distr"
DoCmd.RunSQL "INSERT INTO t_distr (( …Column Names……) " & _
    "SELECT … data to insert….." FROM qPT "
   
OTHER SQL STATEMENT CHANGES HERE-JUST DID NOT SHOW THEM.    
   
qdfSelect.SQL = "SELECT * FROM crs_db.dbo.t_rpt "
DoCmd.RunSQL "DELETE * FROM t_rpt"
DoCmd.RunSQL "INSERT INTO t_rpt ( …Column Names…… ) " & _

CurrentDb.Close
Set qdfSelect = Nothing
    Call PT_t_person
    LinkDatabases ("UnLink")
    If DoesObjectExist("qPT", "Query") Then DoCmd.DeleteObject acQuery, "qPT"
Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    If Err.Number = 3021 Then
        Resume Next
    Else
        MsgBox Err.Number & " Description: " & Err.Description & " Procedure:  proc_qPT_t_distr"
        Resume Exit_ErrorHandler
    End If
End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 Since your using ODBC, the connection will be held for the ODBC pool timeout setting, which I believe is five minutes by default

 Given that, you are most likely having an active connection for 30 minutes.

JimD.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:


  Note that I should have said "open" rather then "active".    You may have the connection, but something need not be occuring at that split second.

  Overall though, if you procedure takes 30 minutes, then you've got a connection for 30 minutes.  The time required to switch from one SQL statement to the next once the first completes would be split seconds.

JimD.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
I was afraid of that.  I originally had each query in its own module that made the connection, processed the data and then destroyed the query and closed the connection.  But am I saving any time, as I just realized, as it loops through the procedures (as they were originally) I still am having a total connection time of 30 minutes, only this time it is broken apart by each of the procedures running separately rather than one long process.  Which would you suggest is the better approach?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Sandra SmithRetiredAuthor Commented:
Thank you, now I just need to determine which is the better approach.
Rey Obrero (Capricorn1)Commented:
ssmith94015,

see if this will do the job for you

How to use ADO disconnected and persisted recordsets
http://support.microsoft.com/kb/195082
Sandra SmithRetiredAuthor Commented:
Thanks Capricorn1.  I am only downloading data locally to ACCESS 2003, distilling for this database's usage - am not sending anything back to the server.  Basically, I download a lot of raw data from the server to local tables, distill it via processes in the ACCESS db and then destroy the original data from the Server and only keep the distilled information.  I would prefer to have this all created as a procedure on the Server and only get the distilled, but I am at a financial institution and it is impossible to get new procedures created for departmental use - so we are limited to getting raw data and using ACCESS/EXCEL for reporting.  Problem is some of this data is huge and takes quite a bit of time to download.  I am exporing different approaches on how to keep connections short, yet get all the information I need a minimal amout of time.  However, I have anotheir client that the information you referred me will be suitable.  I appreciate the link.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thank you, now I just need to determine which is the better approach. >>

  In terms of the connection, both are equivilent.  ODBC connection pooling will be at work unless you turn it off and that is outside of Access.

  Connection pooling was added to Windows because establishing a ODBC conection to a server is an expensive process vs letting a connection sit idle doing nothing, then re-using it latter.  The downside to letting a connection sit idle is that you are consuming resources on the client (memory).    So the default timeout is set for 5 minutes.  After you stop using a connection, the connection is not closed, but is idle.  After 5 minutes is up, the connection is closed if it is not re-used.

  So unless you run a query, then wait 5 minutes, then run another, your going to have a constant connection for all 30 minutes.  Bet that's not a bad thing.

  What you really need to be focusing on is getting the data quicker.  Your already using a pass-through query (and BTW use a query name of "" and it will just be a temp query not saved to the collection) so your are already executing server side.  That leaves you with possibly changing the design of the database (ie. adding indexing or using views to assemble the data in some way) or calling a stored procedure to distill the data before it hands it to you.

 The latter would be the perferable route.

JimD.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.