How can I compact a current database using VBA

[atksamy]

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

[ADezii]

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

1. Tools ==> Options ==> General ==> Compact on Close to automatically Compact/Repair a Database when it is closed.
2. CompactRepair Method of the Application Object

[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

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

[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

From an 'Open Form' in the Current Database, execute the following Statement:

SendKeys "%(TDC)", False

[atksamy]

From an 'Open Form' in the Current Database, execute the following Statement:

SendKeys "%(TDC)", False

When i execute this statement its asking me to choose certificate (digital signature)

[ADezii]

When i execute this statement its asking me to choose certificate (digital signature)

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.

[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.

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

[NeoPa]

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.

[ADezii]

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.

You can Compact/Repair the Active Database (Stand Alone) - I do it all the time.

[missinglinq]

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

[atksamy]

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)>

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.

[ADezii]

The following code will also Compact/Repair the Current Database in Access Versions up to 2003 - unfortunately the OP uses 2007.

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

[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.

Your attachment doesnt seem to work. I suspect its because of the access version

[NeoPa]

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.

[ADezii]

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?