[AccessD] CopyFromRS memory Leak in Excel VBA

" CopyFromRecordset is not a method which is limited to ADO I don't think"


Hehehehe....  You are onto my 'cunning plan #1".  I am going to try a DAO RS for this and see what happens.

I can definitely see XL memory useage getting fatter and fatter on the performance resource monitor.  It is over 2GB RAM when it usually craps out.

This is way OVER what it usually is and should be.  The Memory Usage history line creeps up and up and up until it bugs out.





-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, 21 March 2014 12:08 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com