• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Access vs System Memory

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
Shanan212
Asked:
Shanan212
  • 5
  • 5
  • 3
  • +1
4 Solutions
 
danishaniCommented:
Did you tried doing a Compact & Repaier, usely that decreases the Database size significantly.

Hopes that helps,
Daniel
0
 
Shanan212Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
danishaniCommented:
What version of Access do you have?

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

0
 
Shanan212Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
danishaniCommented:
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
 
Patrick MatthewsCommented:
>>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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Patrick MatthewsCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Look at the number of peeps involved at that lab ....

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

mx
0
 
Shanan212Author Commented:
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
 
Shanan212Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
Shanan212Author Commented:
This at the end point of my function!

SendKeys "%(FMC)", False

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now