469,150 Members | 1,912 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

How can I compact a current database using VBA

atksamy
91
HI,

I am running a few modules of VBA code. In the middle of running the code crashes as Access reaches its max size of 2GB; but, if I compress the database at that point it is only 200MB.

How can i compact the database regularly in the interval

regards
atks
May 14 '10 #1
19 23281
ADezii
8,800 Expert 8TB
@atksamy
  1. Tools ==> Options ==> General ==> Compact on Close to automatically Compact/Repair a Database when it is closed.
  2. CompactRepair Method of the Application Object
May 14 '10 #2
atksamy
91
@ADezii
Thanks for replying

I am looking into a automatic way through vba code. The problem i face is I get a message that this cant be done in vba or when database is open. If i close the database then I wont be able to run the remainder of the code from the module
May 14 '10 #3
ADezii
8,800 Expert 8TB
@atksamy
From an 'Open Form' in the Current Database, execute the following Statement:
Expand|Select|Wrap|Line Numbers
  1. SendKeys "%(TDC)", False
May 14 '10 #4
atksamy
91
@ADezii
When i execute this statement its asking me to choose certificate (digital signature)
May 15 '10 #5
ADezii
8,800 Expert 8TB
@atksamy
  1. This Statement is the equivalent of manually Compacting the Current Database from the Menu System
    • Did you Copy and Paste the line of code 'exactly'?
    • Are you executing the code from an 'Open' Form?
    • What Version of Access are you using?
  2. Download the Attachment and let me know if it works for you.
Attached Files
File Type: zip Compact.zip (12.3 KB, 1663 views)
May 15 '10 #6
atksamy
91
@ADezii
yes i copied the line exactly
i copied it into a module and then am running it. what exactly do you mean by executing it from 'open form'
Currently i am trying using Access 2007
May 15 '10 #7
NeoPa
32,165 Expert Mod 16PB
You cannot compact a database while it is active (running code).

I suggest you use a Front-End / Back-End (FE/BE) type database setup, for any large tables at least. That way you can Compact/Repair the BE database when required from your FE database code.
May 15 '10 #8
ADezii
8,800 Expert 8TB
@NeoPa
You can Compact/Repair the Active Database (Stand Alone) - I do it all the time.
May 15 '10 #9
missinglinq
3,532 Expert 2GB
This site

http://www.mvps.org/access/general/gen0041.htm

provides a simple way of doing this.I should be pointed out, however, that C & R has very, very frequently been indicted as the cause of repeated data corruption, and hence a back up copy should always be made before performing it.

The real question that needs to be addressed here, I think, is why your app is repeatedly growing from 200MB to 2 GB. What exactly are these modules doing?

Linq ;0)>

Addendum: I'm not sure where the notion that you cannot backup an active Access db came from, although I see it all the time. In point of fact, you can do a C & R on the current database by going to Tools - Database Utilities and clicking on Compact & Repair. This is probably the safest way of doing this, as the same menu has an option to backup the current database as well
May 15 '10 #10
atksamy
91
@missinglinq
I am running a set of 30 to 40 queries to filter out some required data. At the end i delete the temporary tables i create.
May 15 '10 #11
ADezii
8,800 Expert 8TB
The following code will also Compact/Repair the Current Database in Access Versions up to 2003 - unfortunately the OP uses 2007.
Expand|Select|Wrap|Line Numbers
  1. CommandBars("Menu Bar"). _
  2. Controls("Tools"). _
  3. Controls("Database utilities"). _
  4. Controls("Compact and repair database..."). _
  5. accDoDefaultAction
May 15 '10 #12
atksamy
91
@ADezii
Your attachment doesnt seem to work. I suspect its because of the access version
May 15 '10 #13
NeoPa
32,165 Expert Mod 16PB
NB. The point made was about an active (running code) database. I'm happy to be shown to be wrong in this, but whenever I've tried it, the code has failed. Admittedly it's been a while since I have tried it, as I generally prefer to C&R into an alternative file first, before renaming back to the original if all has gone well, but I'm pretty confident that it won't work.
May 15 '10 #14
ADezii
8,800 Expert 8TB
why your app is repeatedly growing from 200MB to 2 GB.
linq addressed the real question in Post #10, and in hindsight, nothing else is relevant. Instead or writing Data to Temporary Tables, why not write to Arrays, or better yet, Unbound Recordsets which will store the Data in Memory and avoid the overhead of Temp Tables? Just for curiosity, how many Temp Tables are involved and how large are they?
May 15 '10 #15
NeoPa
32,165 Expert Mod 16PB
I'd say that was one of a number of potential solutions discussed. It wouldn't be my choice.

I would say the most relevant point in this thread is the OP's question. Whatever we may feel is a better approach. These can only be suggested alternatives.
May 15 '10 #16
i'm suspecting the code you use constantly imports and deletes data to and from a table. Access does not automatically recover deleted data.

The best solution is to create a linked table - removed from the database in which you are running the code. You will need to create a new database project for this, and then just the one table in that project.

Import all your data to this linked table. This way all your unrecovered space will be confined to this other table that you don't actually have open. From there, it is easy to run the compactdb on a remote database.
Dec 14 '10 #17
NeoPa
32,165 Expert Mod 16PB
That's a good point to mention in the first paragraph.

The suggested solution is fundamentally about using a FE/BE setup (See post #8), which has much going for it (IMHO). Something that wasn't covered before (as far as I recall) is the importance, in such a scenario, of ensuring the BE has nothing but data in it. The OP mentions in post #5 that certificates (digital signatures) are at least loaded on one of his PCs. It's important, when C&Ring a database, that it has no associated code project, so as to avoid any such issues. The FE database can be C&Red on the development machine and distributed from there at need. It should never need to be done in situ after release.
Dec 15 '10 #18
neelsfer
547 512MB
i sometimes use this code from an icon on desktop to compact. I suppose you can link it to code/macro

Expand|Select|Wrap|Line Numbers
  1. "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\directoryname\filename.accdb" /compact
Dec 15 '10 #19
NeoPa
32,165 Expert Mod 16PB
It wouldn't work in this case though, as that requires exclusive access to the database which, by definition for the Current Database, is already in use. It's helpful information for other possible scenarios though.
Dec 15 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by barbara_dave | last post: by
1 post views Thread by Oded Dror | last post: by
2 posts views Thread by Brian P. Hammer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.