I am using an ODBC connection to a proprietary database
to get data and then I put it in cells on the excel
spreadsheet. I have everything working fine, except
every time I call my query function Excel grows by 30 -
50k of RAM. It gets called every couple of seconds, so
this takes down excel fairly quickly.

I am pretty new to this Visual Basic VBA programming. Is
there some way to free a variable after it is allocated?

Here is the function I narrowed down to be the culprit -

Public Function SendQuery(QryString As String) As
Recordset
Dim PassCount As Integer
PassCount = 0
On Error GoTo QueryError

If Not Connected Then
QueryError:
PassCount = PassCount + 1
If PassCount <= 5 Then
ConnectToMonarch
Else
MsgBox "Unable to Query Monarch"
Exit Function
End If
End If

Set SendQuery = conn.Execute(QryString)

Exit Function

End Function

Any help will be greatly appreciated, Thanks.

I would use either version 2.5 or 2.7 as I think there
are several problems with version 2.6. I have
experienced similar problems and this was resolved by
using 2.7.


-----Original Message-----
I am using an ODBC connection to a proprietary database
to get data and then I put it in cells on the excel
spreadsheet. I have everything working fine, except
every time I call my query function Excel grows by 30 -
50k of RAM. It gets called every couple of seconds, so
this takes down excel fairly quickly.

I am pretty new to this Visual Basic VBA programming.
Is
there some way to free a variable after it is allocated?

Here is the function I narrowed down to be the culprit -

Public Function SendQuery(QryString As String) As
Recordset
Dim PassCount As Integer
PassCount = 0
On Error GoTo QueryError

If Not Connected Then
QueryError:
PassCount = PassCount + 1
If PassCount <= 5 Then
ConnectToMonarch
Else
MsgBox "Unable to Query Monarch"
Exit Function
End If
End If

Set SendQuery = conn.Execute(QryString)

Exit Function

End Function

Any help will be greatly appreciated, Thanks.

.

Are you querying an open Excel workbook? If so you'll want to read:

Microsoft Knowledge Base Article - 319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO
http://support.microsoft.com/default...b;en-us;319998

"Jason" wrote in message ...
I am using an ODBC connection to a proprietary database
to get data and then I put it in cells on the excel
spreadsheet. I have everything working fine, except
every time I call my query function Excel grows by 30 -
50k of RAM. It gets called every couple of seconds, so
this takes down excel fairly quickly.

I am pretty new to this Visual Basic VBA programming. Is
there some way to free a variable after it is allocated?

Here is the function I narrowed down to be the culprit -

Public Function SendQuery(QryString As String) As
Recordset
Dim PassCount As Integer
PassCount = 0
On Error GoTo QueryError

If Not Connected Then
QueryError:
PassCount = PassCount + 1
If PassCount <= 5 Then
ConnectToMonarch
Else
MsgBox "Unable to Query Monarch"
Exit Function
End If
End If

Set SendQuery = conn.Execute(QryString)

Exit Function

End Function

Any help will be greatly appreciated, Thanks.