[AccessD] CopyFromRS memory Leak in Excel VBA

@Darryl, 

Win 7 64 bit running Excel 2010 32 bit referencing ADO 6.0.

Perhaps this is a case of YMMV as you are fond of writing?


I attempted to verify the Excel bug of which you wrote, and instead hit
something which might be related, might not - but was not able to detect a
memory blowup, and I did try exactly the kind of test which I think should
prove or disprove the case, at least as a general rule. Maybe there are some
exceptions or special cases. My code is below.

While I did not detect a memory leak, or at least total memory usage did not
expand and the Excel thread maintained a commit charge of under 50MB, I did
however encounter some kind of weird situation regarding connections.

After an uncertain number of function calls which assigned the connection
object to a variable, VBA went on strike, throwing an runtime error and
refusing to make even one more connection. And that despite resetting VBA. I
had to restart Excel. 

In the ADO documentation Microsoft says there cannot be more than 64
connections, but I don't think this is the result of that, because they are
not live.

But they would support your memory issue statement, if one could argue that
it is a different memory at stake than RAM. But I don't know of any other
kind of memory which would leak, involving an in process thread, except for
RAM.

Is there some other kind? As I said, my RAM usage never changes - and I can
still put lots of other stuff into VBA, I just can't create any more
connections.

I would love to know what *IS* causing the unexpected error, but I can live
without knowing. 

CopyFromRecordset is not a method which is limited to ADO I don't think - I
don't think the Excel issue you are having is driven by that method, but by
the connections upper limit perhaps? I was able to use up 4GB of RAM just in
calling CopyFromRecordset on that million plus record recordset. But the
code I wrote below doesn't call CopyFromRecordset, it just encounters its
own problem with connections.

This stuff has me quite out of my depth. Doesn't take much.

Immediate window last few lines
Closed for the 143 time, found 3599988 records
Closed for the 144 time, found 3599988 records
Closed for the 145 time, found 3599988 records
Closed for the 146 time, found 3599988 records


Option Explicit
Dim m_i_CountRecords As Long

Sub StageOne()
Const LoopMax = 500 'It won't hit this limit, it will die on my machine
Dim i As Long
m_i_CountRecords = 0
'Call function to create connection/recordset
For i = 1 To LoopMax
    DealWithDB
    Debug.Print "Closed for the " & i & " time, found " & m_i_CountRecords &
" records"
Next
End Sub

Function DealWithDB()
Dim iCount As Long
Const MYPATH = "C:\Users\E6530 BILL\Documents\Programming and
Consulting\Excel and Access Development\Lew\TestLarge.accdb"
Const strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" &
MYPATH
Dim MyConn As ADODB.Connection
Dim MyRst As ADODB.Recordset
        Set MyConn = New ADODB.Connection
        Set MyRst = New ADODB.Recordset
        MyConn.ConnectionString = strProvider
        MyConn.Open
        MyRst.Open "Select * from [Sheet2]", MyConn, 1, 1
        MyRst.MoveLast
        m_i_CountRecords = MyRst.RecordCount
Cleanup:
        MyRst.Close
        Set MyRst = Nothing
        MyConn.Close
        Set MyConn = Nothing
End Function