"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.
--