asked on

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

Jim Dettman (EE MVE)

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.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sandra Smith

ASKER

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?

Sandra Smith

ASKER

Thank you, now I just need to determine which is the better approach.

Rey Obrero (Capricorn1)

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 Smith

ASKER

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 (EE MVE)

<<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.