"Out of memory" error

[Shane Suebsahakarn]

Hi all,

This might be one of those things for which there is no workaround. I'm
using A2K2, and using it to perform a very large batch ouput. Both the front
and back ends are MDB files, so no SQL Server involved here. Both SP3 and
Jet SP8 have been updated, and the platform is running on a dual-processor
3Ghz Xeon Win2K3 Server box with 2GB RAM.

The batch run outputs 6 or 7 reports to approximately 16-17,000 snapshot
files None of the reports is particularly complex except for one which
contains 13 subreports, placed in the group headers and footers and are
dynamically hidden based on the contents of a recordset, and two chart
objects. Only one of the reports has a graphic on it (a linked bmp, held in
an image control). During the loop, several recordsets are initialised and
closed (properly set to Nothing).

After approximately 10,000 or so items, I get a "There isn't enough memory
to perform this operation. Close any needed programs...etc.". The only way
to get the operation to continue is to quit Access and restart from the
failed point. Task Manager shows about 65MB in use by msaccess.exe and large
amounts reported as free.

I have looked at a couple of post-SP3 hotfixes, but they don't seem to apply
to this situation as I'm not switching any items between datasheet and
design view or using SQL Server.

If anyone could shed any light on this or suggest a fix/workaround, I'd be
very grateful.

Shane.

[MGFoster]

Shane Suebsahakarn wrote:

Hi all,

This might be one of those things for which there is no workaround. I'm
using A2K2, and using it to perform a very large batch ouput. Both the front
and back ends are MDB files, so no SQL Server involved here. Both SP3 and
Jet SP8 have been updated, and the platform is running on a dual-processor
3Ghz Xeon Win2K3 Server box with 2GB RAM.

The batch run outputs 6 or 7 reports to approximately 16-17,000 snapshot
files None of the reports is particularly complex except for one which
contains 13 subreports, placed in the group headers and footers and are
dynamically hidden based on the contents of a recordset, and two chart
objects. Only one of the reports has a graphic on it (a linked bmp, held in
an image control). During the loop, several recordsets are initialised and
closed (properly set to Nothing).

After approximately 10,000 or so items, I get a "There isn't enough memory
to perform this operation. Close any needed programs...etc.". The only way
to get the operation to continue is to quit Access and restart from the
failed point. Task Manager shows about 65MB in use by msaccess.exe and large
amounts reported as free.

I have looked at a couple of post-SP3 hotfixes, but they don't seem to apply
to this situation as I'm not switching any items between datasheet and
design view or using SQL Server.

If anyone could shed any light on this or suggest a fix/workaround, I'd be
very grateful.

Shane.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

16,000 to 17,000 Snapshot files! I've got to ask, why so many?

Are you sending these files to customers? Each file is their version of
the report?

Are you doing something like this:

Get customer IDs
For each customer ID
Print the customer's annual report
Next customer

If so, maybe you should put a call to the DoEvents function in the
For..Next loop. Maybe that will allow Access to recover memory before
the next report.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiOSnYechKqOuFEgEQLvZACcC58p+EMBmOMeShzi8gD7Yw E6JK4AoImd
TkOe4SkmFV7+JJEeJqTxEbIm
=Vcba
-----END PGP SIGNATURE-----

[Lyle Fairfield]

Shane Suebsahakarn wrote:

Hi all,

This might be one of those things for which there is no workaround. I'm
using A2K2, and using it to perform a very large batch ouput. Both the front
and back ends are MDB files, so no SQL Server involved here. Both SP3 and
Jet SP8 have been updated, and the platform is running on a dual-processor
3Ghz Xeon Win2K3 Server box with 2GB RAM.

The batch run outputs 6 or 7 reports to approximately 16-17,000 snapshot
files None of the reports is particularly complex except for one which
contains 13 subreports, placed in the group headers and footers and are
dynamically hidden based on the contents of a recordset, and two chart
objects. Only one of the reports has a graphic on it (a linked bmp, held in
an image control). During the loop, several recordsets are initialised and
closed (properly set to Nothing).

After approximately 10,000 or so items, I get a "There isn't enough memory
to perform this operation. Close any needed programs...etc.". The only way
to get the operation to continue is to quit Access and restart from the
failed point. Task Manager shows about 65MB in use by msaccess.exe and large
amounts reported as free.

I have looked at a couple of post-SP3 hotfixes, but they don't seem to apply
to this situation as I'm not switching any items between datasheet and
design view or using SQL Server.

If anyone could shed any light on this or suggest a fix/workaround, I'd be
very grateful.

Shane.

Access is a rapid application development platform - desktop database.
Microsoft creates it. One can understand why it is horribly inefficient;
it must accommodate very incapable users, and its creator has never,
IMO, cared anything about efficiency. It's always, "Buy more hardware!".

For your 16000-17000 files Access is likely to be doing enough work for
16000000-17000000, mostly because it's a pig.

A solution might be to write VBA code that creates 16000-17000 html
files, writing each to disk upon completion, and reusing all the memory
variables involved. (Of course, VBA itself is no prize, but that's
another story). I have done something like this with genealogical pages,
and ftp-ed (with code) each file to a remote server upon completion.

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....

[Shane Suebsahakarn]

"MGFoster" <[e-mail]> wrote in message
news:ko*************@newsread3.news.pas.earthlink. net...

16,000 to 17,000 Snapshot files! I've got to ask, why so many?

Are you sending these files to customers? Each file is their version of
the report?

Are you doing something like this:

Get customer IDs
For each customer ID
Print the customer's annual report
Next customer

If so, maybe you should put a call to the DoEvents function in the
For..Next loop. Maybe that will allow Access to recover memory before
the next report.

Essentially yes - the system is producing a number of invoices. Each invoice
has three levels of subrecords, and the complete bill consists of summaries
and itemisations for each subrecord (depending on the printing selection
made), so one bill could be anywhere from one to upwards of a thousand
individual reports. The snapshots are actually a quickly-accessible archive
of the actual print run for customer services agents.

I thought I might have found the solution with some registry tweaks, but it
didn't do the trick. I'll give the DoEvents a shot to see if that improves
things....otherwise it might have to be a redevelopment exercise with
Crystal Reports or something for the next version of the software.

Thanks for the suggestion!

[Shane Suebsahakarn]

Probably - which is why we're upsizing to SQL Server for the next release
(need to do that anyway to accommodate more than 5 million records). I was
considering an ADP as a front end, but now I'm not so sure. The code doesn't
actually use that many variables in itself but there appears to be a memory
leak somewhere. Bizarrely, it runs *better* on my single-processor XP box
with half gig-RAM (it gets to around 15,000 files before stopping).

Much as I hate to admit it, this might just be pushing Access a touch too
far. I don't really want to dip into an external component to create the
reports or generate HTML files for archiving but that might be the only
option.

I suppose I could try A2K3 but from what I've heard so far, I'm not holding
out large amounts of hope as far as its stability is concerned.

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:U7*******************@read2.cgocable.net...

Access is a rapid application development platform - desktop database.
Microsoft creates it. One can understand why it is horribly inefficient;
it must accommodate very incapable users, and its creator has never, IMO,
cared anything about efficiency. It's always, "Buy more hardware!".

For your 16000-17000 files Access is likely to be doing enough work for
16000000-17000000, mostly because it's a pig.

A solution might be to write VBA code that creates 16000-17000 html files,
writing each to disk upon completion, and reusing all the memory variables
involved. (Of course, VBA itself is no prize, but that's another story). I
have done something like this with genealogical pages, and ftp-ed (with
code) each file to a remote server upon completion.

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When writing
new applications, you should avoid using these deprecated components. When
modifying existing applications, you are strongly encouraged to remove any
dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in the
future, whereas MSDASQL will not have any new features added to it, will
not be available on 64-bit, and will not be accessible from the OLE DB NET
Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based standard.
Given the availability of the SOAP Toolkit 2.0, you should migrate from
RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample code for
remotely accessing Microsoft ActiveX® Data Objects (ADO) Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet Engine,
and plans no new releases or service packs for this component. As a
result, the Jet and Replication Objects (JRO) is being deprecated in this
release and will not be available in any future MDAC releases.

....

[Stephen Lebans]

Were the registry "tweaks" you spoke of to turn off the Loading Image
dialog for the specific Image file formats you are loading?

Are you using any Lines or Boxes in your report that have a Border set
to Dots/Dashes etc?

What do you mean by "Only one of the reports has a graphic on it (a
linked bmp, held in an image control)." Why is the Image linked? Does it
need to change on a customer by customer basis.

To pinpoint the issue try:
1) Running your ouput but set the Image control's Visible prop to No.
or
2) Running your ouput but set the Graph's Visible prop to No.

On a final note here is note from my Web site:

' If the file extension is in uppercase then the Office Graphics filters
' will be used to load the image. If the file extension is in lowercase
' then Access does not use the Office Graphics filters. This applies
' to Bitmap and Enhanced Metafiles.
' For Bitmaps you want Access to use the Office Graphics filters
' but for EMF's you do not.
' Notice the opposite results for the handling of Bitmap for EMF Images.
' Uppercase EMF = Office Graphics filter - Poor results for EMF's
containing full color bitmaps
' Lowercase emf = bypass Office Graphics filters - Normal results
' Uppercase BMP = Office Graphics filter - Normal results
' Lowercase bmp = bypass Office Graphics filters - Poor results for
BMP's containing full color bitmaps
' It does not matter the letter case of the Image's file extension on
your hard drive. Access looks at the
' filename you enter via the GUI or programmatically for the Picture
property of the Image control.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Shane Suebsahakarn" <sh_a_n_e@gciXc_om.net> wrote in message
news:d0**********@hercules.btinternet.com...

"MGFoster" <[e-mail]> wrote in message
news:ko*************@newsread3.news.pas.earthlink. net...

16,000 to 17,000 Snapshot files! I've got to ask, why so many?

Are you sending these files to customers? Each file is their version of the report?

Are you doing something like this:

Get customer IDs
For each customer ID
Print the customer's annual report
Next customer

If so, maybe you should put a call to the DoEvents function in the
For..Next loop. Maybe that will allow Access to recover memory before the next report.
Essentially yes - the system is producing a number of invoices. Each

invoice has three levels of subrecords, and the complete bill consists of summaries and itemisations for each subrecord (depending on the printing selection made), so one bill could be anywhere from one to upwards of a thousand
individual reports. The snapshots are actually a quickly-accessible archive of the actual print run for customer services agents.

I thought I might have found the solution with some registry tweaks, but it didn't do the trick. I'll give the DoEvents a shot to see if that improves things....otherwise it might have to be a redevelopment exercise with
Crystal Reports or something for the next version of the software.

Thanks for the suggestion!

[DFS]

Lyle Fairfield wrote:

Access is a rapid application development platform - desktop database.
Microsoft creates it. One can understand why it is horribly
inefficient; it must accommodate very incapable users,
As well as very snide ones...

and its
creator has never, IMO, cared anything about efficiency. It's always,
"Buy more hardware!".
The answer to all bloatware.

For your 16000-17000 files Access is likely to be doing enough work
for 16000000-17000000, mostly because it's a pig.
Oracle 9.2 is a pig on Windows, consuming over 110mb of memory for the
service and SQLPlus. By comparison, Access is a stick figure.

A solution might be to write VBA code that creates 16000-17000 html
files, writing each to disk upon completion, and reusing all the
memory variables involved. (Of course, VBA itself is no prize, but
that's another story). I have done something like this with
genealogical pages, and ftp-ed (with code) each file to a remote
server upon completion.

--
--
Lyle

[Shane Suebsahakarn]

"Stephen Lebans" <Fo****************************************@linval id.com>
wrote in message news:r7********************@ursa-nb00s0.nbnet.nb.ca...

Were the registry "tweaks" you spoke of to turn off the Loading Image
dialog for the specific Image file formats you are loading?
No - I tried changing the desktop heap size (SessionPageView and
SessionPagePool) to see if that made a difference but it didn't work. The
loading dialog doesn't actually appear.
Are you using any Lines or Boxes in your report that have a Border set
to Dots/Dashes etc?
Nope, they all have solid or transparent borders.
What do you mean by "Only one of the reports has a graphic on it (a
linked bmp, held in an image control)." Why is the Image linked? Does it
need to change on a customer by customer basis.
Yes, the image does potentially need to change on a customer by customer
basis (it's either the company logo or the logo of the dealer who sold the
account
- an image does not need to be selected, and the control's Picture property
is set
to a zero-length string in this case; in this particular deployment, no
image was selected).
To pinpoint the issue try:
1) Running your ouput but set the Image control's Visible prop to No.
or
2) Running your ouput but set the Graph's Visible prop to No.
I did actually try completely deleting both the image and the graphs.
Removing
the image had no effect - the batch run ended in approximately the same
place. I
haven't yet tried removing the graphs, but that might do something. Still, I
suspect
that it would only delay rather than resolve the problem, and I can't really
remove
the graphs for the real outputs as many of the customers (well - the ones
who are
billing the most anyway) rely on them.

I'll try removing them first anyhow, to see if it has any effect. If it
does, I may need
to look at an alternative method of presenting the data, although nothing
springs
immediately to mind.

Thanks for the info about the extension (I hadn't seen that bit before on
your site). I'll
code something in to take that into account.
On a final note here is note from my Web site:
' If the file extension is in uppercase then the Office Graphics filters
' will be used to load the image. If the file extension is in lowercase
' then Access does not use the Office Graphics filters. This applies
' to Bitmap and Enhanced Metafiles.
' For Bitmaps you want Access to use the Office Graphics filters
' but for EMF's you do not.
' Notice the opposite results for the handling of Bitmap for EMF Images.
' Uppercase EMF = Office Graphics filter - Poor results for EMF's
containing full color bitmaps
' Lowercase emf = bypass Office Graphics filters - Normal results
' Uppercase BMP = Office Graphics filter - Normal results
' Lowercase bmp = bypass Office Graphics filters - Poor results for
BMP's containing full color bitmaps
' It does not matter the letter case of the Image's file extension on
your hard drive. Access looks at the
' filename you enter via the GUI or programmatically for the Picture
property of the Image control.

[pap]

"DFS" <no****@DFS.com> wrote in message news:1o*************@fe07.lga...
[]> Oracle 9.2 is a pig on Windows, consuming over 110mb of memory for the

service and SQLPlus. By comparison, Access is a stick figure.

LOL

peter walker

[Stephen Lebans]

Shane I believe it is possible to have the graph object save a copy of
itself as a Bitmap/Gif image. YOu could then load this into an Image
control. This would alleviate your issue if it happens to be a Graph
object issue.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Shane Suebsahakarn" <sh_a_n_e@gciXc_om.net> wrote in message
news:d0**********@hercules.btinternet.com...

"Stephen Lebans" <Fo****************************************@linval id.com> wrote in message news:r7********************@ursa-nb00s0.nbnet.nb.ca...

Were the registry "tweaks" you spoke of to turn off the Loading Image dialog for the specific Image file formats you are loading?

No - I tried changing the desktop heap size (SessionPageView and
SessionPagePool) to see if that made a difference but it didn't work.

The loading dialog doesn't actually appear.

Are you using any Lines or Boxes in your report that have a Border set to Dots/Dashes etc?
Nope, they all have solid or transparent borders.

What do you mean by "Only one of the reports has a graphic on it (a
linked bmp, held in an image control)." Why is the Image linked? Does it need to change on a customer by customer basis.

Yes, the image does potentially need to change on a customer by

customer basis (it's either the company logo or the logo of the dealer who sold the account
- an image does not need to be selected, and the control's Picture property is set
to a zero-length string in this case; in this particular deployment, no image was selected).

To pinpoint the issue try:
1) Running your ouput but set the Image control's Visible prop to No. or
2) Running your ouput but set the Graph's Visible prop to No.
I did actually try completely deleting both the image and the graphs.
Removing
the image had no effect - the batch run ended in approximately the

same place. I
haven't yet tried removing the graphs, but that might do something. Still, I suspect
that it would only delay rather than resolve the problem, and I can't really remove
the graphs for the real outputs as many of the customers (well - the ones who are
billing the most anyway) rely on them.

I'll try removing them first anyhow, to see if it has any effect. If it does, I may need
to look at an alternative method of presenting the data, although nothing springs
immediately to mind.

Thanks for the info about the extension (I hadn't seen that bit before on your site). I'll
code something in to take that into account.

On a final note here is note from my Web site:
' If the file extension is in uppercase then the Office Graphics filters ' will be used to load the image. If the file extension is in lowercase ' then Access does not use the Office Graphics filters. This applies
' to Bitmap and Enhanced Metafiles.
' For Bitmaps you want Access to use the Office Graphics filters
' but for EMF's you do not.
' Notice the opposite results for the handling of Bitmap for EMF Images. ' Uppercase EMF = Office Graphics filter - Poor results for EMF's
containing full color bitmaps
' Lowercase emf = bypass Office Graphics filters - Normal results
' Uppercase BMP = Office Graphics filter - Normal results
' Lowercase bmp = bypass Office Graphics filters - Poor results for
BMP's containing full color bitmaps
' It does not matter the letter case of the Image's file extension on your hard drive. Access looks at the
' filename you enter via the GUI or programmatically for the Picture
property of the Image control.

[Shane Suebsahakarn]

Thanks Stephen, I'll give that a go - hopefully it'll do the trick. It
should also have a side effect of speeding up the print run if I preprocess
all of the graphs.

"Stephen Lebans" <Fo****************************************@linval id.com>
wrote in message news:qY********************@ursa-nb00s0.nbnet.nb.ca...

Shane I believe it is possible to have the graph object save a copy of
itself as a Bitmap/Gif image. YOu could then load this into an Image
control. This would alleviate your issue if it happens to be a Graph
object issue.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Shane Suebsahakarn" <sh_a_n_e@gciXc_om.net> wrote in message
news:d0**********@hercules.btinternet.com...

"Stephen Lebans"

<Fo****************************************@linval id.com>

wrote in message

news:r7********************@ursa-nb00s0.nbnet.nb.ca...

> Were the registry "tweaks" you spoke of to turn off the Loading Image > dialog for the specific Image file formats you are loading?

No - I tried changing the desktop heap size (SessionPageView and
SessionPagePool) to see if that made a difference but it didn't work.

The

loading dialog doesn't actually appear.

> Are you using any Lines or Boxes in your report that have a Border set > to Dots/Dashes etc?

Nope, they all have solid or transparent borders.

> What do you mean by "Only one of the reports has a graphic on it (a
> linked bmp, held in an image control)." Why is the Image linked? Does it > need to change on a customer by customer basis.

Yes, the image does potentially need to change on a customer by

customer

basis (it's either the company logo or the logo of the dealer who sold

the

account
- an image does not need to be selected, and the control's Picture

property

is set
to a zero-length string in this case; in this particular deployment,

no

image was selected).

> To pinpoint the issue try:
> 1) Running your ouput but set the Image control's Visible prop to No. > or
> 2) Running your ouput but set the Graph's Visible prop to No.

I did actually try completely deleting both the image and the graphs.
Removing
the image had no effect - the batch run ended in approximately the

same

place. I
haven't yet tried removing the graphs, but that might do something.

Still, I

suspect
that it would only delay rather than resolve the problem, and I can't

really

remove
the graphs for the real outputs as many of the customers (well - the

ones

who are
billing the most anyway) rely on them.

I'll try removing them first anyhow, to see if it has any effect. If

it

does, I may need
to look at an alternative method of presenting the data, although

nothing

springs
immediately to mind.

Thanks for the info about the extension (I hadn't seen that bit before

on

your site). I'll
code something in to take that into account.

> On a final note here is note from my Web site:
> ' If the file extension is in uppercase then the Office Graphics filters > ' will be used to load the image. If the file extension is in lowercase > ' then Access does not use the Office Graphics filters. This applies
> ' to Bitmap and Enhanced Metafiles.
> ' For Bitmaps you want Access to use the Office Graphics filters
> ' but for EMF's you do not.
> ' Notice the opposite results for the handling of Bitmap for EMF Images. > ' Uppercase EMF = Office Graphics filter - Poor results for EMF's
> containing full color bitmaps
> ' Lowercase emf = bypass Office Graphics filters - Normal results
> ' Uppercase BMP = Office Graphics filter - Normal results
> ' Lowercase bmp = bypass Office Graphics filters - Poor results for
> BMP's containing full color bitmaps
> ' It does not matter the letter case of the Image's file extension on > your hard drive. Access looks at the
> ' filename you enter via the GUI or programmatically for the Picture
> property of the Image control.

[Shane Suebsahakarn]

I just thought I'd post back - I've managed to alleviate the problem
(if not fix it) by opening the reports hidden in design view prior to
the batch output, so that they remain open for the duration of the
operation. It seems that the opening and closing of the reports is
the thing that is causing the memory leak, whereas effectively
switching views gives the proper outputs without the error.

Thanks all for your help, it helped me to track down an alternative
method.