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

sql excel vba persistent
Matt Victory picture Matt Victory · Jul 10, 2011 · Viewed 10.5k times · Source

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

Kevin Pope picture Kevin Pope · Mar 29, 2012

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.

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More