Error - make more resources available

[Brenda from Michigan]

Access 2002 SP3 - likely developed originally in Access 97

User is in and out of the database and is entering large amounts of text
into a Comments field. If there is already a lot of text, she frequently
crashes with the message, "Microsoft Access won't be able to undo this action
to make more resources available so that access an record further design
actions, close and reopen this window."

When she reopens, oftentimes she can now enter more text. I've thought of
running Compact and Repair, but are there any other options besides that?
The user says this has been a problem for about a year.

 

[Clifford Bass]

Hi Brenda,

Certainly do a compact and repair. But make a backup first, just to be
safe. If there is VBA code in the database you also may wish to try a
decompile / recompile (see <http://www.granite.ab.ca/access/decompile.htm>
for details).

If the database is used by multiple users you should split the data
from the forms/queries/reports/code (search your on-line help for "split
database"; also see <http://allenbrowne.com/ser-01.html> and
<http://www.granite.ab.ca/access/autofe.htm> for some information). You may
want to do it anyway even if there is only one user.

Finally, you say there is a "lot of text" in some records. It may make
sense to separate out the memo field from the main table (an assumption) and
place it into a separate table, that possibly allow for multiple memo rows
for each main table row; possibly allowing for the date/time-stamping of the
notes. Something like this:

If you main table is:

tblMain
RecordID (primary key)
etc.....

You could have a notes table:

tblNotes
MainRecordID (long integer) (contains related RecordID from tblMain)
NotesDateTime (date/time)
Notes (memo)

MainRecordID along with NotesDateTime would make up the primary key of
tblNotes.

This would have the effect of keeping the size of each individual note
smaller.

And of course, it may be that adding memory to the user's machine would
make a significant difference; especially if the user's computer has less
than 512MB.

Hope that helps,

Clifford Bass

 

[Brenda from Michigan]

Thanks for the information. We'll give it a try.

 

[Clifford Bass]

Hi Brenda,

You are welcome. And good luck!

Clifford Bass