[AccessD] Fwd: Transpose out of memory error - 20,000 entries

Bill Benson bensonforums at gmail.com
Tue Feb 26 08:09:16 CST 2019


There were no access operations since copyfromrecordset statement which was
pretty early in the routine. Just vba. The 20,000 cells being copied to
(via their Value property) or the size of string data in the array seemed
problematic for VBA but once I closed the recordset and set to nothing, it
no longer was.

In my 20 plus years developing MS and Excel integrations I maybe have seen
this a handful of times, usually when doing a LOT of operations on a very
large recordset.

Recordsets usually dont  use a lot of memory, else Access would choke when
querying large tables. However CopyFromRecordset may force all data into
memory and Access might hang onto that memory after the method is invoked.
Setting to nothing seemed to free up memory is all I am reporting.

>
> 1. "Out of memory" can stem from running out of locks (MaxLocksPerFile
> setting).
>
> 2. It might have also been process address space.
>
>   One of the reasons that Microsoft is now recommending 64 bit is because
> it
> allows for more process address space.   The "out of memory" trend is
> growing with latter versions of Office because Office is getting bigger and
> bigger. When running in 32 bit mode, it's limited to 2GB of process space
> even on a 64 bit OS.   A 32 bit process can access more memory if its
> "large
> address aware", which increases the limit to 3GB.   Outlook and Excel are
> LAA (not sure about Word), but Access is not, so 32 bit Access is still
> stuck at 2GB.
>
>  It's a current request on Access User Voice
> (
> https://access.uservoice.com/forums/319956-access-desktop-application/sugge
> stions/32586994-make-32-bit-access-large-address-aware-laa
> <https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/32586994-make-32-bit-access-large-address-aware-laa>)
> to make 32 bit
> Access LAA.
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Tuesday, February 26, 2019 1:33 AM
> To: Microsoft Access Database Discussion List; Access Developers discussion
> and problem solving
> Subject: [AccessD] Fwd: Transpose out of memory error - 20,000 entries
>
> Automating Excel from MS Access in Office 15 with 8GB RAM.
>
> I was using copyfromrecordset from an Access DAO recordset to an excel
> range. I was doing some cleanup in the automated excel instance. I often
> assign a range to a variant, loop elements, change strings, then paste back
> to the range using transpose. But Access ran out of memory. Many of the
> 20,000 elements had thousands of characters. After scratching my head
> awhile I realized maybe I should close the recordset and set the variable
> to nothing after using CopyFromRecordset. That did the trick.
>
> Just thought I would pass this along.
> --
> 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
>


More information about the AccessD mailing list