How do I make an ADODB.Connection Persistent in VBA in Excel?

Question

I have an Excel spreadsheet that needs to display data from our SQL database.

I am storing the results of a slow query in a temporary table and want to be able to access those results repeatedly without having to rerun the slow query.

I am using an ADODB.Connection in VBA to connect to our SQL database and retrieve information. I want to open a connection once and use that session across various macros for as long as the user is using the spreadsheet. I can't find any way to make the connection persistent, so it always closes once I exit the macro that opened it and I lose any temporary tables that I created.

Answer 1

Daz Lewis is entirely correct that you shouldn't be keeping database connections open indefinitely. However, if this is a local database and you're the only one using it, then you should be able to keep it open as long as you please.

Add this as the top line of the module holding your code:

Global dbConnPublic As ADODB.Connection

In the "ThisWorkbook" object:

Private Sub Workbook_Open()
    Set dbConnPublic = openDBConn() 'Or whatever your DB connection function is called'
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    dbConnPublic.Close
End Sub

This will open the db connection on opening the workbook and will close it on closing the workbook. Keep in mind you may want to check to make sure the connection is still open whenever you need to use it as it could get closed by the server if it's open for too long.