Solved

Access vs System Memory

Posted on 2011-09-22
15
332 Views
Last Modified: 2012-06-21
Hi there,

I am running a database which outputs 'many' records that it crashes the db at a point when the output table (when saved) size reaches 1.99gb.

What I mean my 'when saved' is that; after crashing/showing error msg, I save the db and the db size is 1.99gb. This is ok since thats the maximum an access db can take.

Now what I did is, cleared the output table, and tried to run the query again and the same msg (error msg) popped up.

When I cleared the output table and saved the db, its still 1.99gb. This is why the error still showing up.

Is there anyway to make the db go down in size without saving it under different name? (considering I already cleared the output data from the output table)

Thanks!
0
Comment
Question by:Shanan212
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 12

Assisted Solution

by:danishani
danishani earned 334 total points
ID: 36582958
Did you tried doing a Compact & Repaier, usely that decreases the Database size significantly.

Hopes that helps,
Daniel
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36582989
Nope, I couldn't find that feature in Access. When I resaved it, it went down to 9mb file :o

But this is a hassle since I cleared whatever that was output and it still shows 1.99gb.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 166 total points
ID: 36582996
"size reaches 1.99gb."
Approaching the 2GB limit is like approaching a Black Hole (seriously).  As noted above - try Compact & Repair (frequently).  BUT ... make a backup first.  C&R might take a which on a db that size.

Records deleted (and any object for that matter) are only 'flagged' for deletion - which does not actually occur until the next Compact & Repair.

mx

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Assisted Solution

by:danishani
danishani earned 334 total points
ID: 36583022
What version of Access do you have?

In Access 2007, you find it under > Windows Button > Manage > Compact and Repair Database.

0
 
LVL 13

Author Comment

by:Shanan212
ID: 36583030
I see!

That answers many questions!

Is there any way to compact and repair using vba? such that I can run it via a button?

Thanks!
0
 
LVL 75
ID: 36583052
Note that you cannot do a C&R on a db that is in use, ie ... someone has the db open, and you try to do a C&R externally.  But yes, it can be done via vba.

mx
0
 
LVL 12

Expert Comment

by:danishani
ID: 36583105
Another question, is your database split by any change?

I guess not, that might also helps you maintaining your database in a propper way.
You Data will be stored at your Backend and the Forms, Reports , Querys in FrontEnd.

Lots of advantages of doing this, see for more info this thread by HightechCoach:
http://www.hitechcoach.com/index.php?option=com_content&view=article&id=35:split-your-access-database-into-application-anddata&catid=65:split-database

Hope this helps,
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36583221
>>Approaching the 2GB limit is like approaching a Black Hole (seriously).  

Does that make it more like the Schwarzchild Radius, or the Chandrasekhar Limit?

:)

(All silliness aside, it is a most dangerous limit to approach.)
0
 
LVL 75
ID: 36583250
Better yet:

http://www.reuters.com/article/2011/09/22/us-science-light-idUSTRE78L4FH20110922

I predicted this 30 years ago.  Einstein's theory never made sense to me.  Imagine the ramifications  of this if it holds up!!!  

mx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36583406
MX,

Until another lab replicates the result, I refuse to believe it.  This has been one of the most thoroughly tested theories in the history of physics :)

Patrick
0
 
LVL 75
ID: 36583516
Look at the number of peeps involved at that lab ....

If that doesn't pan out, then we are doomed.

mx
0
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 0 total points
ID: 36587247
Since you cannot compact and repair via VBA command, what I did is this: This calls the Alt+key for compact & repair command.

So when a user clicks "Clear Tables" button on my form.

I am running a query that will clear the tables (hence they are marked for deletion/cleared; but their data remains within the db)

Then at the end of that event, I added this to initiate compact and data.

I tried to show a message saying "Data cleared!" but the below code didn't work. The code has to be after the warning message!

SendKeys "%(FMC)", False
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36715240
My coding above goes around access to compact data via button click.

DoCmd.Close acForm, Me.Name, acSaveNo

Above coding is recommended to close the current form
0
 
LVL 75
ID: 36588126
"Since you cannot compact and repair via VBA command, "
Well, you can, but not from the db you are in,  You need to do it from another db.


"I added this to initiate compact and data."
\What did you add?

mx
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36588470
This at the end point of my function!

SendKeys "%(FMC)", False

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question