What is the VB code to compact a database? 6

[DirtyRed]

I would like to be able to insert code that will automatically compact the db after it updates my tables. I use Microsoft Scheduler to update my tables during the night so i will have updated data in the morning. I always like to compact the database after each update. I would appreciate some assistance from some of you programmers out there with the code to accomplish this. I thought I saw a previous post but unable to find it.

 

[CPUburn]

hi there,

on this computer there unfortunately is no msoffice available, but I have a line of code in a program of mine which does this...

I thought it went like:

docmd.compactdatabase original.mdb newdatabase.mdb

after which you can rename the new database to the old database (I use 2 databases for reason of security; if there's a problem with the original db and the compact doesn't work, otherwise it might be gone)

IF the line of code above would not be correct, I'll let ya know in about a week's time, I'll have the chance by than to look it up and re-post this answer.

the kid

 

[Turbo]

DirtyRed,

In the code window place your cursor on the word "compact" and press "F1", this will open the help for that word. Below is the structure of the line of code you will need to compact the database:

DBEngine.CompactDatabase olddb, newdb, locale, options, password

I have had trouble compacting the DB I am that I am currently in so you might want to build a backend DB and compact that DB from the one you are in currently.

I hope this will help. Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[lewi]

I know this post is a little late but if you haven't found what you need try this.

If you are processing the updates at night via a scheduler, the easiest way to compact is to use the /compact command line switch. i.e \path to access\msaccess.exe \path to database\mydb.mdb /compact.
This will compact the database and then exit access.
Just add the command to your schedule after the updates.
make sure that you specify the full path to the database

Hope this helps.

Lewi

 

[waldemar]

But isnt there a way to compact without this hazzle to point to or rename the new database?
How does access operate the menu function? I would just like to call that compact function without having to include the menu - inside some vba module.... is there a simple way?

regards
waldemar

 

[dleiba]

Place the following code on the On_Click Event Procedure of a button.

SendKeys "%W1"
SendKeys "%TDC"

It's been working quite well for me. Let me know how it goes.

Dominic >:O>

 

[CPUburn]

FYI

access does rename the db, but when compact finishes, it renames it back
grtz
CPUburn