SQL Server Connection Pooling

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!

Opening a connection is separate from executing any query, and needs to come
first. Before you can run the query ADO needs to contact your data source
and read its schema (that is, needs to know the table names and structure) -
this is what occurs when you first connect. Then you can do the query.

After making the connection, to run a query, you need something to store the
results of your select query, i.e. an ADODB.Recordset variable:
Dim CMPW_Recordset as ADODB.Recordset
Then you need to actually execute the query, which can be done either with
the Connection's Execute method:
Set CMPW_Recordset = CMPW_Connect.Execute("SELECT ..." ...)
or the Recordset's Open method:
Set CMPW_Recordset = New ADODB.Recordset
CMPW_Recordset.Open("SELECT ...", CMPW_Connect, ...)
The ... indicates there are other parameters, which depend on your DB and
what you need to do; in particular there are properties of the recordset that
need to be set properly, such as the CursorType, CursorLocation. You will
need to refer to ADO help to figure these out (in MSDN library if you do not
have any help files on your computer). But hope you can get a start with the
brief instructions here.

"James W." wrote:

> 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!
>

Should have added: be sure to close the Recordset when done!

"K Dales" wrote:

> Opening a connection is separate from executing any query, and needs to come
> first. Before you can run the query ADO needs to contact your data source
> and read its schema (that is, needs to know the table names and structure) -
> this is what occurs when you first connect. Then you can do the query.
>
> After making the connection, to run a query, you need something to store the
> results of your select query, i.e. an ADODB.Recordset variable:
> Dim CMPW_Recordset as ADODB.Recordset
> Then you need to actually execute the query, which can be done either with
> the Connection's Execute method:
> Set CMPW_Recordset = CMPW_Connect.Execute("SELECT ..." ...)
> or the Recordset's Open method:
> Set CMPW_Recordset = New ADODB.Recordset
> CMPW_Recordset.Open("SELECT ...", CMPW_Connect, ...)
> The ... indicates there are other parameters, which depend on your DB and
> what you need to do; in particular there are properties of the recordset that
> need to be set properly, such as the CursorType, CursorLocation. You will
> need to refer to ADO help to figure these out (in MSDN library if you do not
> have any help files on your computer). But hope you can get a start with the
> brief instructions here.
>
> "James W." wrote:
>
> > 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!
> >