I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Any thoughts? Dennis

Hi dennis,

I clear my recordset variable after each query. I

make sure that you set _all_ objects to nothing when you finished your code!
Esp. the connection string.

If you have many queries with one connection string then make sure that you
open the connection only once, run your queries and close the connection.
This is a lot faster the open-query-close-open-query-close...

After a while and lots of queries your Excel will be slow and using a lot of
memory anyway. However, you can work much longer without troubles when you
do what I mentioned.

regards

arno

"arno" wrote ...

make sure that you set _all_ objects to nothing when you finished your code!
Esp. the connection string.

Any particular reason for this advice? My experience is that if you
let the objects go out of scope, ADO cleans up impeccably.

Jamie.

--

Hi Jamie,

make sure that you set _all_ objects to nothing when you finished your
code!
Esp. the connection string.

Any particular reason for this advice?

run queries eg. in a loop and never cleanup. each query will take longer and
longer and the memory usage of excel (task manager) will grow and grow.

impeccably.
sorry, I do not know this word. anyway: do not clean up = wait.

arno

"arno" wrote ...

run queries eg. in a loop and never cleanup. each query will take longer and
longer and the memory usage of excel (task manager) will grow and grow.

That's not my experience. Please post your code that reproduces this
e.g. using northwind.

Many thanks,
Jamie.

--

Hi Jamie,

run queries eg. in a loop and never cleanup. each query will take longer
and
longer and the memory usage of excel (task manager) will grow and grow.

That's not my experience. Please post your code that reproduces this
e.g. using northwind.

My experience is different. I am running ADO requests vs. an Access mdb that
contains only links (ODBC, whatever is neccessary) to tables like Foxpro,
text, xl-files and other mdb. In queries I can link tables form different
sources (a kind of mini-datawarehouse), so Northwind does not help. And I
run queries vs. not saved excel-files that contain the data from other
queries (named ranges, not sheets). I use all kinds of sql (select, insert,
update, union, full outer joins etc) to all kinds of tables. When I do _not_
cleanup the objects (esp. connection string and recordset) the size of
excel.exe will grow and queries will be slower. When I cleanup - everything
is fine.

The second point is that I run multiple queries on my mini-datawarehouse
within one macro. I found it a lot faster to open the connection, run all
queries and close the connection instead of open-run-close-open-run-close...

Still, excel.exe is growing and queries take longer, however, this happens
very very slowly. Note, I am runing thousands of queries a day, that's my
job. The reason for this could be "normal" excel bahaviour that everyone
knows when opening lots of files, some small files, some with megabytes of
data, runing code...

There's nothing special in my code, I have functions for the connection
string and for the query. These functions are called from the
"report"-makros that do the rest.

This is why I am giving the advise to cleanup and run many queries by
opening only one connection as this works perfectly in my rather complex
environment. These tips should work anywhere. when you follow the KB-"Howto"
samples for ADO performance issues should not arise but maybe some people do
not care too much about cleaning up variables/objects. In many cases not
cleaning up does not have any consequences but with ADO queries it does.

regards

arno

don't know if this is your situation, but you might check this out:

http://support.microsoft.com/default...98&Product=xlw
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

--
Regards,
Tom Ogilvy


"Dennis" wrote in message
...
I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Any thoughts? Dennis

"Dennis" wrote ...

I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Are you querying an open workbook?

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

If this applies, save the worksheet to a temporary workbook, close it
and query the closed workbook. Here's some example code:

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"MySheet"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

' <<do something with recordset

rs.Close
Con.Close

End Sub

Jamie.

--

Thank you for your help. I will give your comments a try.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!