I am trying to optimize my vba procedure to use connetion pooling when
connecting to the sql server. My question is after I have made my connection
and set that as a global variable how to I then use it to perform a select
statement.

I thought it would be as simple as CMPW_Connect.open with my SQL select
stament following but no luck. I believe my connection pooling code is
correct I just need help retrieving the data later in the application.

Public CMPW_Connect As ADODB.Connection


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Comments: Creates a pooled connection to a SQL Server database.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Public Sub ConnectToDatabase()

Const sSOURCE As String = "ConnectToDatabase"

Dim lAttempt As Long
Dim sConnectCMPW As String
Dim sConnectBPCS As String
'On Error GoTo ErrorHandler

' Create the connection string to CMPW database
sConnectCMPW = "Provider=SQLOLEDB;" & _
"Data Source=WC0337\HWS;" & _
"Initial Catalog=HWS;" & _
"Integrated Security=SSPI"

Set CMPW_Connect = New ADODB.Connection
CMPW_Connect.ConnectionString = sConnectCMPW
CMPW_Connect.Open
CMPW_Connect.Close

Many thanks!