Out of Memory (1 Viewer)

User gets an "Out of Memory error" when running a query, but there is plenty of RAM and free drive space.

Sub Button49_Click ()

DoCmd SetWarnings False

DoCmd OpenQuery "qrymktbl RegionBudvsAct"
'creates table Tbl RegionBudvsAct

DoCmd RunMacro "macupd tblRegionBudvsAct"

DoCmd OpenQuery "qrymktbl RegionBudvsActYTD"
'creates table tbl RegionBudvsActYTD

DoCmd OpenQuery "qrydel tblRegionBudvsActYTDzeros"

DoCmd RunMacro "macupd tblRegionBudvsActYTD"

DoCmd SetWarnings True

MsgBox "Main Table Complete!!!"

End Sub

The error is on: DoCmd RunMacro "macupd tblRegionBudvsActYTD"

Try importing the db into a new db, and see if that does it.

How to

I am not that familiar with access, please bear with me and tell me how to import the database into a new database.

I had a similar problem, and did the import, and it was OK again.

Here's what you do:

Create a new database from the template or file menu.

After the new database has been created, go to File -> External Data -> Import. A file browser should open, and select the troublesome db.

Select all the objects in tables, queries,form..........,modules. I think you should see a button saying something like 'More Details'
Click it, and select check the Relationships and data. Perform the import.

You should now have a new db with all the objects and data.

See if you still have problems with the 'out of memory'

Note! I'm using a norwegian access, so the names used above is freely translated, so they might vary from what I've wrtten.

'Out of memory' can be caused by a myriad of problems.

First, you need to find the System Info accessory, which is often under System Tools (but might be elsewhere for some versions of Windows.) This tool is going to help you figure out what kind of memory you are out of.

You need to know a few things:

How much swap space is available? This depends in turn on the way you have set up Virtual Memory. Is this set for a fixed upper limit of disk space? Is it on a disk that is low on free space?

How much physical memory does the system have? If you are running Windows after Win98 on a 64 Mb system, maybe that is not a lot.

What are your resource levels before/after you start Access. If your resources reach something like 90% occupied, you can run out of that particular flavor of memory very quickly. Even God doesn't know why Bill Gates allowed the "resource pool" to have a fixed memory size - but it does.

Then, there is the "garbage collection" problem. Access does not always close everything automatically. Everything it keeps open takes up memory. If any of the macros you run happen to run code that opens recordsets or something like that, you could have lots of trouble.

Also, if any of the queries are "compounded" - i.e. a query based on another query rather than on a table. The deeper the number of queries implied by a query, the worse off you are.

Finally, what other applications are involved when Access is running? For instance, does Access open Excel or Word when this process is being run? There goes more memory.

Now, the $ 64,000 question: How do you find the problem? The 64-cent answer is, you might NEVER really find it. Memory problems are notoriously hard to find if you get past the first couple of factors I listed. About the only sure-fire way to fix this class of problem once it gets past the easy stuff is to throw hardware at it - more disk, more RAM.

Oy Vey

I tried to import the database and My Access has been locked up for 5 hours. What to do, what to do. The last post has left me really dissillusioned.

Sorry to hear about the lockup, but I've never experienced any troubles importing db's. I read an article somwhere about 'insufficient memory' and one of the remedies was importing. This has something to do with the GDI HEAP which I believe The Doc_Man is refering to with the 'garbage collection'

I'll see if I can find the link.

When it comes to your lockup - post more info.

Have you compilled and saved all modules, and compacted the database?

how-to

How do I compile and save all modules? When you refer to modules are you referring to the individual queries?

If you have VBA in your db, you are beeing asked if you want to save them when closing the Visual Basic Editor.

If you look under 'Debug' menu you'll find 'Compile'. If this command is greyed out, then it's compiled or you don't have any VBA.

The *Compact and Repair' is under Tools -> Databasetools.

Make a backup before doing this.

The article I was refering to in previous post is

here.

Select *Access Online Encyclopedia' and search for Article code 'G3'.

Geralf is right, I forgot to warn you about compacting.

You see, the garbage collection problem comes in TWO flavors. The Resources collection problem, and the Temp Space problem. When you have a Resources problem, that is outside of Access. But the Temp Space problem you can attack.

Look at the size of the database. Watch it grow. (If the DB is in use, it will be faster than watching grass grow, trust me.) This is because every query, every report or form implying a query, and every time you open a recordset, you create a temporary object that is stored in the database. When you close the object with which that recordset/query/form/report was associated, you mark the object for deletion. But you do not actually delete it. It sits inside the DB file waiting for someone to run compaction.

The down side of this is that the virtual memory required to run the database depends on the size of the DB file, which can grow under conditions as I have described. So a really, really good bit of advice came from Geralf. COMPACT that DB if you can.

Now, there is a bit of a caveat here. If you are already getting the "out of memory" message, your DB might be too big to compact. Besides that, if your disk is close to full, you might not have enough disk space to build the compacted version while the un-compacted version still exists. Unfortunately, this is crucial to the entire operation. (I've learned THAT one painfully.)

Just so it's said: The compact was suggested by Rich, and not by me.

Virus?

Both Access and Excel are prone to generate memory errors with a number of viruses.