"Out of memory Error"

[Fearl3$$]

Hi there, I wonder if anyone can help me with a problem. I am having.

I will try to explain it as easy as I my self understand the database/application.
One of Our Companies has a Custom application, that Connects to a MS Access DB. When you open the application, there is an option to access all the data, without generating any report.
Most of the Numeric data are located in one table, and the Description To the numericdata are linked to a few other Tables.


So when you want to see all data without specifying what data you want the db gathers all the data from the different tables, and puts it into a table that you can export to a MS excel file.

Now what happens is, when gathering the data, the table/db runs out of memory. and give a "Out of Memory" ERROR after about a minute of trying to put all the data together.

I am not a Expert in Databases, but from what I know ,usually this happens because there are not enough memory allocated to the Db or its tables.
On a SQL server you can allocate more memory to your db/tables to execute a query. But I have no idea how to allocate more memory to a MS access db/table.

The machine this app & db is running on has got sufficient memory : 3gb of memory. I have also set a 35gb page file on the pc, so that I know for a fact the page file is big enough....

If any one knows a solution to this problem, I have, please advise.

Thank you.

[RuralGuy]

Welcome to AccessForums. Is it the Custom Application that is giving you the error or do you actually run the Access db directly from the desktop?

[Fearl3$$]

We use the custom app for the front end, to connect to the access db.
It is not the application that give's me the error. I tested it on another newly installed machine, with a db that doesn't have a lot of data on it. then the data gathers quick and easy, with out the memory error.
But the moment I use the db with the large amounts of data, the db runs out of memory, when not specifying specific data.

[RuralGuy]

Can you run the Access db directly? If so, can you do a Compact and Repair and then try it again with your application?

[Fearl3$$]

I can Run the Db directly,I already dit a Compact & Repair, with no luck. I also did a Preformance Check, and did the chacges it told me I must do.
Some one else told me that I must set the db & app's Priority to HIGH, but that did not work either.

I have cleaned out about 400 000 records, & and now the application does not give me a "Out of memory" error.

There was about 1 260 000 records in the Table. all Crusial data. I made a backup before I cleaned out the records.
On another machine, where I loaded the db+ app, there are about 930 000 records in the table and no error yet, it only takes a while to populate. The second machine is alot slower than the other one.

At the moment it looks to me Like there is a limit to the ammout of records you can have in a MS Access db?
If that is the case, can I link 2 db's to check data from eachother?

[RuralGuy]

There is a 2GB limit to the size of the mdb. The size will change as queries are run. You can certainly link mdb's and each one has the 2GB limit.

[marianne]

probably you have so many applications running in your computer. you can see it on your taskbar or your windows task manager! if there are so many applications running, you certainly will run out of memory because ms access application also eats up lot of memory. try to check out your windows task manager.

[Fearl3$$]

There are enough recources for access. I took the Db home last night, to do a Test on my machine at home.: Intel Core2 E8200,4gb low latnacy memory, and tons of hdd space.
Os are using only 256mb ram, and no Cpu recorces. Same problem exists,
but with less records, about 900 000, it works.

It looks to me like there are a limit to the amount of records you can have in Access?

[RuralGuy]

What is the size of the db that has the large table in it?

[Fearl3$$]

It was 392mb, but after it was compressed it is now round about 159mb.

Someone gave me this link : You may receive a "There isn't enough disk space or memory" error message when you perform an operation on an Access table
Because it worked for them.
I did what was explained there, but it did not help at all.

I have split the db up, and exported the querie tables + all the other tables, but that data does not display in the new db. The tables and everything are the same. the Size of the db file grows, but no data is shown. So I guess I must be doing something wrong.(although everything is being done in wizzards.)

[RuralGuy]

Your last post confused me a bit. Do you know what is being executed in Access that is causing the Memory Error? The size of your db is not particularly large so I would not expect this type of problem. Because of the temporary tables needed by Access are internal to one db, it is certainly possible to create queries that can max out a db. This can usually be resolved by changing some of the methodology.

[RuralGuy]

BTW, wizards are not particularly efficient in what they create. They were designed to give people a starting place.

[Fearl3$$]

I know what is causing the memory error.

it is a Query that Runs, that gathers all the data from the different tables, and puts it all together in a table the query creates.

Running the Query directly in the db, works fine, Ii do not get a memory error there. But when that same query is run by the front end app, the db gives me a "Out of memory" error, when there are more than 1 000 000 records. I can run any other query in the frontend app, with no problems at all.

[Fearl3$$]

Soz for the confusing post, I was a bit confused there aswell. hehe.

Like I said, I am no expert on this topic, and are trying to understand what is going on.

[RuralGuy]

If your front end ap is just running one query in the db, how about posting the SQL for that query here? Maybe we can see something.