Out of Memory (Error 7) when adding a VBA function

[Jay]

Hi all -

WinXP/Access2003/All updates installed/2 GB of memory/Late model Dell PC.

I'm getting an unexpected "Out of memory" error when I try to add a new
function to a module. Pressing the [Help] button on this error dialog
informs me that the error number is “7†and suggests things like: procedures
are too complex and the like, but I have built much more complex VB code
along with more modules than this in the past. I've been editing the file
for some time, adding code for new functions and procedures at will with no
trouble. In fact, I’ve been building the file on two different PC’s with no
trouble.

Today, for some reason, adding a new function resulted in the ‘out of
memory’ error (when I try to type “function test() and press enter). Note
that I can ocassionally add a new function, but only if its name does not
start with the same characters as an existing function. Also, I can add a
blank module or two, but then the same error occurs when trying to add a
third module. I can add statements to existing procedures and functions with
no trouble.

My application is not overly complex; I have 3 modules each containing 1 to
3 procedures with a 4th module containing 10 fairly basic functions (each
function has 3 to 25 statements or so) and there are no other applications
running.

Any advice from others who have experienced this problem would be appreciated,
Jay

 

[Allen Browne]

A decompile might fix this.

Try this standard sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all -

WinXP/Access2003/All updates installed/2 GB of memory/Late model Dell PC.

I'm getting an unexpected "Out of memory" error when I try to add a new
function to a module. Pressing the [Help] button on this error dialog
informs me that the error number is “7†and suggests things like:
procedures
are too complex and the like, but I have built much more complex VB code
along with more modules than this in the past. I've been editing the file
for some time, adding code for new functions and procedures at will with
no
trouble. In fact, I’ve been building the file on two different PC’s with
no
trouble.

Today, for some reason, adding a new function resulted in the ‘out of
memory’ error (when I try to type “function test() and press enter). Note
that I can ocassionally add a new function, but only if its name does not
start with the same characters as an existing function. Also, I can add a
blank module or two, but then the same error occurs when trying to add a
third module. I can add statements to existing procedures and functions
with
no trouble.

My application is not overly complex; I have 3 modules each containing 1
to
3 procedures with a 4th module containing 10 fairly basic functions (each
function has 3 to 25 statements or so) and there are no other applications
running.

Any advice from others who have experienced this problem would be
appreciated,
Jay

 

[Daniel Pineault]

I would start by doing a backup of the db (better safe than sorry),
performing a compact and repair on the database, performing a compile, then
trying again.

You could also, import all of your db objects into a new db to see if that
solves the probelm.
--
Hope this helps,

Daniel Pineault
For Access Tips and Example: http://www.cardaconsultants.com/en/msaccess.php
If this post was helpful, please rate it by using the vote buttons.

 

[Jay]

Hi Daniel -

Your suggestions worked. Many, many thanks. I'll be trying Allen's
suggestion as well, but I thought I'd respond to let you know that your
advice solved the problem. I will definitely add your solution to my Access
'toolbox'.

Does this fix imply a general file corruption or any area in specific? It
appears that Allen's approach targets several potential problem areas and
I'll be following his instructions as well, to learn what I can about this
kind of 'corruption.'

Thanks again,
Jay

I would start by doing a backup of the db (better safe than sorry),
performing a compact and repair on the database, performing a compile, then
trying again.

You could also, import all of your db objects into a new db to see if that
solves the probelm.
--
Hope this helps,

Daniel Pineault
For Access Tips and Example: http://www.cardaconsultants.com/en/msaccess.php
If this post was helpful, please rate it by using the vote buttons.

Hi all -

WinXP/Access2003/All updates installed/2 GB of memory/Late model Dell PC.

I'm getting an unexpected "Out of memory" error when I try to add a new
function to a module. Pressing the [Help] button on this error dialog
informs me that the error number is “7†and suggests things like: procedures
are too complex and the like, but I have built much more complex VB code
along with more modules than this in the past. I've been editing the file
for some time, adding code for new functions and procedures at will with no
trouble. In fact, I’ve been building the file on two different PC’s with no
trouble.

Today, for some reason, adding a new function resulted in the ‘out of
memory’ error (when I try to type “function test() and press enter). Note
that I can ocassionally add a new function, but only if its name does not
start with the same characters as an existing function. Also, I can add a
blank module or two, but then the same error occurs when trying to add a
third module. I can add statements to existing procedures and functions with
no trouble.

My application is not overly complex; I have 3 modules each containing 1 to
3 procedures with a 4th module containing 10 fairly basic functions (each
function has 3 to 25 statements or so) and there are no other applications
running.

Any advice from others who have experienced this problem would be appreciated,
Jay

 

[Jay]

Hi Allen -

Thanks for the rapid response and comprehensive troubleshooting method. As
noted above, Daniel's solution worked in this case, but I will run my
corrupted file through your methodology at first chance and will report back.

Many thanks,
Jay

A decompile might fix this.

Try this standard sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all -

WinXP/Access2003/All updates installed/2 GB of memory/Late model Dell PC.

I'm getting an unexpected "Out of memory" error when I try to add a new
function to a module. Pressing the [Help] button on this error dialog
informs me that the error number is “7†and suggests things like:
procedures
are too complex and the like, but I have built much more complex VB code
along with more modules than this in the past. I've been editing the file
for some time, adding code for new functions and procedures at will with
no
trouble. In fact, I’ve been building the file on two different PC’s with
no
trouble.

Today, for some reason, adding a new function resulted in the ‘out of
memory’ error (when I try to type “function test() and press enter). Note
that I can ocassionally add a new function, but only if its name does not
start with the same characters as an existing function. Also, I can add a
blank module or two, but then the same error occurs when trying to add a
third module. I can add statements to existing procedures and functions
with
no trouble.

My application is not overly complex; I have 3 modules each containing 1
to
3 procedures with a 4th module containing 10 fairly basic functions (each
function has 3 to 25 statements or so) and there are no other applications
running.

Any advice from others who have experienced this problem would be
appreciated,
Jay

 

[Jay]

Hi Allen -

As you suggested, a decompile solved the problem. I performed the
operartions on my corrupted database in the order you outlined. After each
step, I tested the database and found that the corruption was not 'cleared
up' until I decompiled.

Thank you for the comprehensive procedures and supplemental information
sources. You've armed us against one of the deadliest of database evils.

Thanks again,
Jay

A decompile might fix this.

Try this standard sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all -

WinXP/Access2003/All updates installed/2 GB of memory/Late model Dell PC.

I'm getting an unexpected "Out of memory" error when I try to add a new
function to a module. Pressing the [Help] button on this error dialog
informs me that the error number is “7†and suggests things like:
procedures
are too complex and the like, but I have built much more complex VB code
along with more modules than this in the past. I've been editing the file
for some time, adding code for new functions and procedures at will with
no
trouble. In fact, I’ve been building the file on two different PC’s with
no
trouble.

Today, for some reason, adding a new function resulted in the ‘out of
memory’ error (when I try to type “function test() and press enter). Note
that I can ocassionally add a new function, but only if its name does not
start with the same characters as an existing function. Also, I can add a
blank module or two, but then the same error occurs when trying to add a
third module. I can add statements to existing procedures and functions
with
no trouble.

My application is not overly complex; I have 3 modules each containing 1
to
3 procedures with a 4th module containing 10 fairly basic functions (each
function has 3 to 25 statements or so) and there are no other applications
running.

Any advice from others who have experienced this problem would be
appreciated,
Jay