Out of memory

[Bob Richardson]

My DB has about 65,000 records, each with 70 fields. There is only one user
using the DB. Is this a volume that will always choke Access 2003? I've been
getting an 'out of memory' message when I try to do some simple edits (like
changing one field from text to integer). Will buying more memory (going
from 512MB to 1 gig) really help?

 

[Douglas J Steele]

How much free space is on your hard drive (or, more explicitly, on whatever
hard drive is pointed to by the TEMP and TMP Environment Variables)?

How big is your MDB before you try making the changes?

FWIW, I'd hardly call changing a field from text to integer "a simple edit":
under the covers, Access adds a new field, populates that new field from the
old field, then deletes the old field.

 

[Guest]

65000 records is a drop in a bucket. However I've seldom seen a table that
needed more than 30 fields in a properly normalized database. A normalized
database is very efficient where an unnormalized databases isn't. This might
be the root of your memory problems.

I'd also recommend turning off Name AutoCorrect if you are doing things like
changing datatypes. It's going to make your job harder; but might stop the
memory errors.

 

[Bob Richardson]

The table really had 85 columns. It was easy to split this into two tables
and now everything works well. Thanks.

 

[Bob Richardson]

The C: drive has 35 GB free!!!

The MDB file is normally 128MB, but can compress to about 61MB.

I had 52 fields that were generally 0,1, or -1. I moved these to a separate
table and all seems to work well.

 

[John Vinson]

The table really had 85 columns. It was easy to split this into two tables
and now everything works well. Thanks.

Just another suggestion - as Douglas said, changing a datatype
requires that Access keep TWO copies *of the entire table* in memory
simultaneously, apparently (well, one copy at least). To get around
this, if you do need to change field datatypes in a table of any
substantial size, it's better to create a new, empty table with the
new datatypes, and run an Append query to migrate the data. This
handles the change one record at a time rather than doing the whole
table in one big indigestible bolus.

John W. Vinson[MVP]

 

[Tony Toews]

My DB has about 65,000 records, each with 70 fields. There is only one user
using the DB. Is this a volume that will always choke Access 2003? I've been
getting an 'out of memory' message when I try to do some simple edits (like
changing one field from text to integer).

Were you doing many field changes one after the other? The first
bunch worked but then it started giving the out of memory error? If
so exiting, compacting and re-entering would likely fix that problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm