"Out of memory" errors occur when, for some reason, Access runs out of memory. (OK, guys in the back room, no snickering....) So what actions can lead to this?
Well, the first thing to consider is that memory comes in two flavors for Access. There is the object that you actually store in the database. Then there is an object that represents something external to the database file but that exists for the duration of Access execution in RAM.
I believe this error applies to something that is NOT a stored database object like a table, query, etc. etc. It is more likely a dynamic object. For instance, the kind of object you create using CreateObject("Application.Excel") or whatever other apps you might use.
You could also do this with a dynamic array created by calling a subroutine in a module where that module locally declares a huge array.
Basically, you have run into a memory limit that is going to be either physical memory (for something that currently cannot be paged), virtual memory (because the swap file is full), or program buffer memory (because you reached a limit on the number of allowed dynamic data buffers.) This will be hard as heck to figure out. There are a few places to look, though.
Usually, you can get to some useful data in XP to help diagnose some of these problems. Your best tools will be these items:
Run >> Accessories >> System Tools >> System Information
and
Run >> Control Panel >> System >> Advanced (tab) >> (Performance) Settings >> Advanced (tab) and look at the bottom for Virtual Memory.
and
Task Bar (right-click) >> Task Manager >> Performance (and >> Processes)
From System Information on the System Summary screen (top of the display tree) several lines down (just after Time Zone) you will see several lines devoted to memory issues. That gives you an overview of how much physical and virtual memory you are using. If you drilled down through Software you could get a list of what is physically present in memory. Be prepared to be sick at all the crud that Bill Gates allows into memory.
From the Task Manager Performance screen, you can see how much physical memory you have left. It is in the lower part of the display under the graphs.
From Task Manager Processes, you can see what processes are in physical memory and what priority has been assigned to each.
From the Control Panel path leading to Virtual Memory, you can see how much of your disk has been set aside for page/swap space. If you have less space set aside than 1/2 of your system's total physical memory, you have too little. Many authorities say you need an amount equal to your physical memory and I don't disagree.
You get an "out of memory" error when you have depleted the memory on your system and too many things of higher priority (device drivers, e.g.) are loaded to memory. Access will try to keep as much as it can in memory but some things (like dynamically created objects) cannot ever go into the MDB file. For instance, your WORKSPACE must be resident. Open file buffers must stay resident. Device driver data structures must stay resident.
Now, the question is whether you have things active in your database that are space eaters. Like opening BE/FE files. (OK, good idea but takes up extra memory for a second set of buffers for the extra workspace.) Like having linked objects active (because active object context ain't cheap). Like having pictures active (because picture mapping is very memory-intensive). Like having a really complex form active (because if it is busy enough, the collection of controls that has to be in memory for an open form can get quite big.
Things that eat your socks behind the scene are dynamic string creation and deletion in VBA modules. The string paradigm is such that strings are dynamically pulled from a structure called the HEAP. If you replace a string with a new string without overlaying it, you essentially deallocate a string, allocate a new string, and adjust the string variable to point to the new string value. Even though the old string value is de-referenced, it is not always safe to deallocate it. So old strings build up a lot. This might be relevant to what you are doing if it is a string value that always locks you up. The question is, is it always the same string value for the same record, or has something been found that changes WHICH record stops you. (Not which line of code... which RECORD that the code was handling.)
If you exhaust the heap or otherwise heavily constrain it by using it a lot, you can deplete memory. If you have a lot of subroutines with complicated private variable declarations, you use up a lot of stack space. The stack and the heap start from opposite ends of your virtual address space and work towards each other, generally. If they ever meet, you are out of memory.
Why didn't I give you more specifics? Because everyone's probems are very different. I can only tell you principles and places to look. You are the one who has to sit in front of the keyboard to shut down the situation that causes this problem.
