Excel VBA SQL

[Custom Creations For Kids]

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]

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

 

[Dcolecpa]

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

 

[Nate Oliver]

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/comp.databases.ms-access/msg/cf56a67de1562ab4

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

 

[Nate Oliver]

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