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

Please Login or Register  to view this content.

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.