adodb connection to sql server.. How long does it stay open when idle

Hi.
Question. In vba... every time I run a query against an sql server, I open the connection, run my query, then close the connection. If I have One function do that, and then another function do a different query, For speed, I think it would be best to not close the connection between function calls. Maybe create a Global Connection that both functions could run against. But, how long will the connection stay open between calls??? I am worried that if I do this, at some point, the connection may go idle and error/close. Could I do a connection check to see if it is still open?

I am all about speed - keeping my apps as fast as possible. The apps run globally and Asia's speed is slow.

My startup will run the two functions one after the other. Both use the same connection. So, Why should I open/close the connection in the 1st sub and open/close in the next sub??

Write_Login_History(Get_Name_From_ID()) ' query sql server to get name then write to sql server


code example of 1st fuctions

Public Function Get_Name_From_ID(id As String) As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim select_sql As String
Dim from_sql As String
Dim where_sql As String
Dim sql_string As String

Dim table_name As String

    
    cn.Provider = "sqloledb.1" ' integrated security
    cn.Properties("Data Source").Value = "XXXXXXXX" ' production integrated security production
    cn.Properties("Initial Catalog").Value = "YYYYYY" ' integrated security
    cn.Properties("Integrated Security").Value = "SSPI" ' integrated security
    cn.CursorLocation = adUseClient
    cn.Open
    
    select_sql = "Select upper([Last_Name] + ', ' + Preferred_first_name) as Name "
    from_sql = "from dbo.GED_DIM_NETWORK_USER_V_DND "
    where_sql = "where NETWORK_USER_ID = '" & id & "'"
    sql_string = select_sql & from_sql & where_sql
    
    rs.Open sql_string, cn, adOpenKeyset, adLockOptimistic 'this allows add new record
    Get_Name_From_ID = rs("Name")
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
End Function

Thanks
Steve
Harrisburg, PA

If you have two routines run one after the other using the same connection, there isn't really any point in closing and reopening it in between. I doubt that the connection pooling on the server would have actually cleaned up the connection in that time anyway even if you did. You should avoid keeping the connection open unnecessarily, but I can't see any benefit in closing it in between two immediately consecutive routines.