Maintain an Open Connection to SQL with Excel VBA

I'm not sure if what I'm wanting to know is possible or not. But I use the following code to open and connection, and then I query that connection.

Public Sub OpenConnection(Datasource As String, DatabaseName As String)

    Set zConnection = New ADODB.Connection

    zConnection.ConnectionString = "Driver={SQL Server};" & _
                                   "Server=" & Datasource & ";" & _
                                   "Database=" & DatabaseName & ";" & _
                                   "Trusted_connection=yes;"
                               
    zConnection.Open
    
End Sub

zConnection is a global variable

I'm using that connection here to get a record set of everything that returns from the sql code.

Public Function GetQueryRecordset() As ADODB.Recordset
    
    On Error GoTo FUNC_ERR
    
    Dim t As Integer: t = 1
    
GET_RST:
    Dim rst As New ADODB.Recordset
    
    Set rst.ActiveConnection = zConnection
    rst.Source = Me.Code
    rst.Open
    
    Set GetQueryRecordset = rst
    
FUNC_EXIT:
    Exit Function
    
FUNC_ERR:
    If Error = -2147217871 And t < 5 Then
        t = t + 1
        GoTo GET_RST
    Else
        MsgBox "Error Numuber: " & Err.Number & vbLf & Err.Description
        End
    End If
    
End Function

This works great, but I'm considering putting this inside a custom function I can use within an Excel cell. Problem is, it is slow to open the connect every single time. But I'm wondering if there is a way to open that connection, and then keep it open and then grab it every time I need to use it.

Basically, I have no idea how to persistently save and access that connection. so I can use it over and over again without reconnecting.

---------EDIT---------

There were a few questions that came up that I wanted to address. I originally left them out so I wouldn't complicate the question.

So I have created a custom class called sqlClass. This class has useful functions that allow me to put in SQL code into the object, display the code in a readable fashion within the debugger, but also open and close connections, put a table in that is connected to a sql query, or return a record set with that data. So saying it is a global variable isn't accurate, but it is a class variable I have so any action taken in that instance of the object can use whatever connection the object established.

Now that I'm wanting to use that in a cell function, I was wanting to reduce the number of times that connection was opened and closed. I'm actually thinking I'd put code in workbook open and workbook close that would take care of opening and closing the connection.

It sounds like my assumption that Set zConnection = New ADOBD.Connection was the actual connection was wrong. I thought every New ADOBD.Connection was the connection, but from what I'm hearing from the comments is that ADOBD.Connection is more of a "bridge" to where the connections are at and when I make a New this only makes a new bridge not a brand new connection. Feel free to correct me if I'm wrong on this, I will be testing that out next and maybe make another edit if necessary.

---------EDIT 2---------

Here is the complete sqlClass custom class I made.

Option Explicit

'***********************************************************************************
'SqlClass helps hold SQL code and gives convientent functions to call that SQL code.
'Requires Reference: Microsoft ActiveX Data Objects x.x Library
'***********************************************************************************

Private zLines As New Collection
Private zConnection As ADODB.Connection

Public Sub Add(ByVal sqlLine As String)

'**************************************************************************************
'    DESCRIPTION:
'       This will add a line of SQL as a string to the collection
'
'    INPUT VARS:
'       sqlLine: The string of SQL code to add to the bottom of the collection
'**************************************************************************************
    
    Dim addSql As String: addSql = sqlLine
    'Makes sure that the right is always a space since this will not hold SQL code with new paragraphs.
    If Right(addSql, 1) <> " " Then
        addSql = addSql & " "
    End If
    
    zLines.Add addSql

End Sub

Public Sub Blank()

'**************************************************************************************
'    DESCRIPTION:
'       This will add a element to the collection that contains a vbnullstring. This
'       only helps when trying to view the code in a readable form (printsql)
'
'    INPUT VARS:
'       n/a
'**************************************************************************************

    zLines.Add vbNullString
    
End Sub

Public Sub Clear()

'**************************************************************************************
'    DESCRIPTION:
'       This will clear all code from the collection
'
'    INPUT VARS:
'       n/a
'**************************************************************************************
    
    Set zLines = New Collection

End Sub

Public Function Code() As String

'**************************************************************************************
'    DESCRIPTION:
'       This returns a string showing the full SQL code held within this Class instance.
'       NO PARAGRAPHS SHOWN
'
'    INPUT VARS:
'       n/a
'**************************************************************************************
    
    Dim str As String

    Dim i As Integer
    For i = 1 To zLines.Count
        str = str & zLines(i)
    Next
    
    'Remove double spaces, to reduce size of string
    Do Until InStr(str, "  ") = 0
        str = Replace(str, "  ", " ")
    Loop
    
    'Excel can only send a query to the SQL Server of 32,767 or less, this will throw an error on purpose so you know this is what cause the issue.
    If Len(str) > 32767 Then
        Dim xxx As Integer: xxx = 1000000 'errors on purpose
    End If

    Code = str

End Function

Public Sub PrintSql()

'**************************************************************************************
'    DESCRIPTION:
'       Prints SQL code in the Immediate Window, this will show each line as a new line
'       For debug purposes
'
'    INPUT VARS:
'       n/a
'**************************************************************************************
    
    Dim i As Integer
    For i = 1 To zLines.Count
        Debug.Print zLines(i)
    Next

End Sub

Public Sub CreateQueryTable(ws As Worksheet, Datasource As String, initialCatalog As String)

'**************************************************************************************
'    DESCRIPTION:
'       This submits the query to the SQL server and makes the results a table on the
'       selected worksheet
'
'    INPUT VARS:
'       ws:             The worksheet that gets the table
'       DataSource:     The address of the SQL Server
'       initialCatalog: This seems to be used with error message, I use it to say which database inside the server I'm pulling from.
'**************************************************************************************
    
    Dim wkStation As String: wkStation = VBA.Environ("computername")
    
    'Values are largely default, look up this function to learn more about the inputs. Each variable sent is a new element in the array.
    Dim qryTbl As QueryTable: Set qryTbl = ws.ListObjects.Add(SourceType:=xlSrcExternal, _
                                                              Source:=Array("OLEDB;", _
                                                                            "Provider=SQLOLEDB.1;", _
                                                                            "Integrated Security=SSPI;", _
                                                                            "Persist Security Info=True;", _
                                                                            "Data Source=" & Datasource & ";", _
                                                                            "Use Procedure for Prepare=1;", _
                                                                            "Auto Translate=True;", _
                                                                            "Packet Size=4096;", _
                                                                            "Workstation ID=" & wkStation & ";", _
                                                                            "Use Encryption for Data=False;", _
                                                                            "Tag with column collation when possible=False;", _
                                                                            "Initial Catalog=" & initialCatalog), _
                                                              Destination:=ws.Range("A1")).QueryTable
                                                              
    'These are also largely default
    'Refresh BackgroundQerry = false means that the table will not update everytime the workbook is opened or
    'something changes to trigger a refresh.
    'This is ideal if you want data from exactly when it was run, not just always up to date.
    With qryTbl
        .CommandType = xlCmdSql
        .CommandText = Me.Code
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Public Sub OpenConnection(Datasource As String, DatabaseName As String)
    
'**************************************************************************************
'   DESCRIPTION:
'       Opens a connection to the SQL server and database to have Code run off of it
'
'   INPUT VARS:
'       DataSource:   The address of the SQL Server
'       DatabaseName: The database name within the server
'**************************************************************************************

    If zConnection Is Nothing Then Set zConnection = New ADODB.Connection

    zConnection.ConnectionString = "Driver={SQL Server};" & _
                                   "Server=" & Datasource & ";" & _
                                   "Database=" & DatabaseName & ";" & _
                                   "Trusted_connection=yes;"
                                   'Driver defines what type of source it is connecting to
                                   'Server is the address of the SQL
                                   'Database is which database within that Server
                                   'Trusted_connection means use the user that is logged into this PC
                               
    zConnection.Open
    
End Sub

Public Sub CheckConnection()
    
'**************************************************************************************
'   DESCRIPTION:
'       Checks if the connection object exists and creates it if not. Also checks if the
'       database is conenected, if not connects it.
'
'   INPUT VARS:
'       n/a
'**************************************************************************************

    If zConnection Is Nothing Then Set zConnection = New ADODB.Connection
    
    If zConnection.State <> adStateOpen Then
        OpenConnection Datasource:="xxxxxxx", _
                       DatabaseName:="xxxxxxxx"
    End If

End Sub

Public Sub CloseConnection()
    
'**************************************************************************************
'   DESCRIPTION:
'       Closes the connection made by OpenConnection
'
'   INPUT VARS:
'       n/a
'**************************************************************************************

    zConnection.Close
    
End Sub

Public Function GetQueryRecordset() As ADODB.Recordset
    
'**************************************************************************************
'   DESCRIPTION:
'       This will create an ADODB.recordset from the SQL code and server and return it
'       as a recordset object.
'
'   INPUT VARS:
'       n/a
'**************************************************************************************
    
    On Error GoTo FUNC_ERR
    
    Dim t As Integer: t = 1
    
GET_RST:
    Dim rst As New ADODB.Recordset
    CheckConnection
    Set rst.ActiveConnection = zConnection
    rst.Source = Me.Code
    rst.Open
    
    Set GetQueryRecordset = rst
    
FUNC_EXIT:
    Exit Function

FUNC_ERR:
    If Error = -2147217871 And t < 5 Then
        t = t + 1
        GoTo GET_RST
    Else
        MsgBox "Error Numuber: " & Err.Number & vbLf & Err.Description
        End
    End If
    
End Function

This is the customer cell function I have made:

Public Function GET_JDE_PN(custPN As String) As String

    Application.EnableEvents = False
    Dim sql As New sqlClass
    
    With sql
        .Add "SELECT DISTINCT"
        .Add "    Field0"
        .Add "FROM"
        .Add "    [Table_Name]"
        .Add "WHERE"
        .Add "    Field1= '" & custPN & "'"
        .Add "    OR"
        .Add "    Field2= '" & custPN & "'"
    End With
    
    Dim rst As ADODB.Recordset: Set rst = sql.GetQueryRecordset
    
    Dim i As Integer
    Do Until rst.EOF
        i = i + 1
        If i = 2 Then
            GET_JDE_PN = "**Multiple Returns**"
            Exit Function
        End If
        rst.MoveNext
    Loop
    
    rst.MoveFirst
    GET_JDE_PN = rst(0)
    Application.EnableEvents = True

End Function

And here is a screenshot of it being used in the workbook:

This does work, however it takes 3-5 seconds to connect to the database each time. And I'd really like to get it to the point that it only connects once and then reuses the existing connection. I'm not sure if I need more code to tell it to leave it open or if the problem is server side. I would like to change the driver from {SQL Server}, but so far I'm not finding a suitable alternative that works with my google searches.