Excel VBA SQL

We have used VBA in Excel 2002 to create an application making data
calls to a SQL database. Initially the application runs quickly making
multiple select statements to get the data into Excel. The longer the
application is used without being closed the longer each data call
takes. Once the Excel workbook is closed and reopened we are back to
very quick SQL calls again. We are using adodb connections to connect
to the database (see example below). It seems that there is a thread
that is not closing out for some reason, that is disconnected once we
close Excel.

Any ideas how to kill the connection/thread so that we can retain the
fast performance speed?

Thanks so much!

***********************************************
Sub selectData(strSELECT As String, strWksht As String, strStartCell As
String)
'Use this as a generic SELECT statement to return data and copy it
starting in the cell strStartCell
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String

strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=****;INITIAL CATALOG=****;"
strConn = strConn & " user Id=****;Password=*****;"
cnPubs.Open strConn
Dim rsPubs As ADODB.Recordset
Dim oCmd As New ADODB.Command
Dim oRSet As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
.ActiveConnection = cnPubs
.Open strSELECT
Worksheets(strWksht).Range(strStartCell).CopyFromRecordset rsPubs
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
********************************************************

Hello,

Two initial suggestions:

1) Delete: Dim oCmd As New ADODB.Command
2) Switch:

cnPubs.Close
Set rsPubs = Nothing

to:

Set rsPubs = Nothing
cnPubs.Close

Regards,
Nate Oliver

Just a thought, you left a couple of objects behind

Dim oCmd As New ADODB.Command
Dim oRSet As ADODB.Recordset

if you set them to nothing also it may help.

Cheers


"Custom Creations For Kids" wrote:

> We have used VBA in Excel 2002 to create an application making data
> calls to a SQL database. Initially the application runs quickly making
> multiple select statements to get the data into Excel. The longer the
> application is used without being closed the longer each data call
> takes. Once the Excel workbook is closed and reopened we are back to
> very quick SQL calls again. We are using adodb connections to connect
> to the database (see example below). It seems that there is a thread
> that is not closing out for some reason, that is disconnected once we
> close Excel.
>
> Any ideas how to kill the connection/thread so that we can retain the
> fast performance speed?
>
> Thanks so much!
>
> ***********************************************
> Sub selectData(strSELECT As String, strWksht As String, strStartCell As
> String)
> 'Use this as a generic SELECT statement to return data and copy it
> starting in the cell strStartCell
> Dim cnPubs As ADODB.Connection
> Set cnPubs = New ADODB.Connection
> Dim strConn As String
>
> strConn = "PROVIDER=SQLOLEDB;"
> strConn = strConn & "DATA SOURCE=****;INITIAL CATALOG=****;"
> strConn = strConn & " user Id=****;Password=*****;"
> cnPubs.Open strConn
> Dim rsPubs As ADODB.Recordset
> Dim oCmd As New ADODB.Command
> Dim oRSet As ADODB.Recordset
> Set rsPubs = New ADODB.Recordset
> With rsPubs
> .ActiveConnection = cnPubs
> .Open strSELECT
> Worksheets(strWksht).Range(strStartCell).CopyFromRecordset rsPubs
> .Close
> End With
>
> cnPubs.Close
> Set rsPubs = Nothing
> Set cnPubs = Nothing
> End Sub
> ********************************************************
>
>

Nate Oliver wrote:
> Switch:
>
> cnPubs.Close
> Set rsPubs = Nothing
>
> to:
>
> Set rsPubs = Nothing
> cnPubs.Close

Closing and setting the ADO objects to Nothing just as they are about
to go out of scope will have no effect.

I think the OP may benefit by keeping a single connection open for the
duration of the application e.g. using an application role on the
server side.

Jamie.

--

Just as Dcolecpa do I fail to see where you are using the oCmd and the oRSet objects in your program. If you don't use them, delete the code lines that Dimmed them.
Besides even if you would have Set oCmd = Nothing a new oCmd object would have popped up because you explicitely asked for a new in in the Dim statement.
(As opposed to Dim oCmd AS ADODB.Command / Set oCmd = New ADODB.command). I suspect that this statement causes a memory leak.

Obviously if you're going to pass multiple SQL queries to a DB you do it
within one connection, versus adding the overhead of opening and closing the
connection. This is a simple matter of optimization.

Will it help you with a memory leak? Not likely.

Is closing an ADO Recordset before it goes out scope a good idea? Yes, it
may very well do something. If you terminate objects in the incorrect order,
you may very well find yourself with a hanging object in memory, this leads
to problems down the road. Rely on VB[A] to terminate the object for you?
You're rolling the dice. In good faith, I can't recommend such practice. And,
it turns out I'm not the only person who feels this way, e.g.,

http://groups-beta.google.com/group/...56a67de1562ab4

Your reply to my post does pique my curiosity, Jamie. Are you trying to help
me? I’m not sure if it’s apparent, but I'm not the one with the problem.

Perhaps the OP would like your attention.

Regards,
Nate Oliver

> Closing and setting the ADO objects to Nothing just as they are about
> to go out of scope will have no effect.
>
> I think the OP may benefit by keeping a single connection open for the
> duration of the application e.g. using an application role on the
> server side.
>
> Jamie.

Nate Oliver wrote:
> Obviously if you're going to pass multiple SQL queries to a DB you do
it
> within one connection, versus adding the overhead of opening and
closing the
> connection. This is a simple matter of optimization.
>
> Will it help you with a memory leak? Not likely.

It may help because one open connection is easier to track than
multiple connections.

> > Closing and setting the ADO objects to Nothing just as they are
about
> > to go out of scope will have no effect.
>
> Is closing an ADO Recordset before it goes out scope a good idea?
Yes, it
> may very well do something. If you terminate objects in the incorrect
order,
> you may very well find yourself with a hanging object in memory, this
leads
> to problems down the road.
> And,
> it turns out I'm not the only person who feels this way, e.g.,
>
>
http://groups-beta.google.com/group/...56a67de1562ab4
>

I agree that closing a recordset may be a good idea and I wouldn't
recommend otherwise but I don't think bad things are going to happen if
an open connection/recordset goes out of scope. Did you notice your
link refers to the DAO? There are specific design faults in DAO which
do not exist in ADO. Consider this quote from Matt Curland (Advanced
Visual Basic 6, P110):

"[DAO provides] another example of poor teardown code. DAO
has Close methods that must be called in the correct order, and the
objects must be released in the correct order as well (Recordset before

Database, for example). This single poor object model behavior has led
to the misconception that VB leaks memory unless you explicitly set all

the local variables to nothing at the end of a function. This is a
completely false notion in a well-designed object model. VB can clear
the variables faster at the End Sub line than you can from code, and it

checks the variables even if you explicitly release your references.
Any effort you make is duplicated."

In my experience, ADO is a 'well-designed object model' and I have not
had any bad experiences when I have inadvertently let an open/active
ADO connection go out of scope. As ever, if you can demonstrate
otherwise it will be well received.

> Rely on VB[A] to terminate the object for you?
> You're rolling the dice. In good faith, I can't recommend such
practice.

Setting an object to Nothing is another matter and has been discussed
many times before. One camp seems to mistrust the garbage collector and
feels there is no harm in always explicitly setting their object
variables to nothing 'just in case'. The other camp realizes the
gargbage collector does its job in clearing up all object variables,
even the ones which are created behind the scenes (e.g. each time
With..End With is used) which cannot be explicitly released in code,
and sees unnecessary teardown code as noise which obscures those rare
occasions (e.g. your DAO example) when it is required.

> Your reply to my post does pique my curiosity, Jamie. Are you trying
to help
> me?

I'd prefer you didn't proliferate myths and you can expect me to lobby
you when you do. It's the Usenet way <g>.

Jamie.

--

> I agree that closing a recordset may be a good idea and I wouldn't
> recommend otherwise but I don't think bad things are going to happen if
> an open connection/recordset goes out of scope. Did you notice your
> link refers to the DAO? There are specific design faults in DAO which
> do not exist in ADO. Consider this quote from Matt Curland (Advanced
> Visual Basic 6, P110):

Indeed, the part where Michael mentions DAO tipped my hand.

> "[DAO provides] another example of poor teardown code. DAO
> has Close methods that must be called in the correct order, and the
> objects must be released in the correct order as well (Recordset before
>
> Database, for example). This single poor object model behavior has led
> to the misconception that VB leaks memory unless you explicitly set all
>
> the local variables to nothing at the end of a function. This is a
> completely false notion in a well-designed object model. VB can clear
> the variables faster at the End Sub line than you can from code, and it
>
> checks the variables even if you explicitly release your references.
> Any effort you make is duplicated."

DAO's not the only object model that will burn you by terminating in the
incorrect order, Excel will too.

http://www.mrexcel.com/board2/viewtopic.php?t=82203

> I'd prefer you didn't proliferate myths and you can expect me to lobby
> you when you do. It's the Usenet way <g>.

Fantastic! What myth? The OP had two garbage variables and the termination
order was questionable.

> Setting an object to Nothing is another matter and has been discussed
> many times before. One camp seems to mistrust the garbage collector and
> feels there is no harm in always explicitly setting their object
> variables to nothing 'just in case'. The other camp realizes the
> gargbage collector does its job in clearing up all object variables,
> even the ones which are created behind the scenes (e.g. each time
> With..End With is used) which cannot be explicitly released in code,
> and sees unnecessary teardown code as noise which obscures those rare
> occasions (e.g. your DAO example) when it is required.

I'll go with the 'mistrust camp' and terminate and close that which I open,
call me a cynic. <g>

Cheers,
Nate Oliver

Nate Oliver wrote:
> What myth? The OP had two garbage variables and the termination
> order was questionable.

To separate myth from fact, post some code that I can run to observe
that closing/releasing these ADO objects in a 'wrong' order causes
something 'bad' to happen.

Jamie.

--