Bill Benson
bensonforums at gmail.com
Thu Mar 20 21:05:07 CDT 2014
I am pulling the data from access into Excel in the code I wrote.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, March 20, 2014 10:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] CopyFromRS memory Leak in Excel VBA
Ok... But I am going the other way. Excel is pulling the data from Access.
Not sure if that makes a difference or not...
-----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:42 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] CopyFromRS memory Leak in Excel VBA
Well, substitute your favorite database in MY code and watch it... see if it
has to do with your implementation. Cuz it sure ain't happening here!
Bill out!
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, March 20, 2014 9:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com