Compacting Back-End Automatically

[KornGeek]

My Back-End sometime gets bloated, and I'm looking for ways to compact it. I've tried diet, exercise... Umm... Enough about me. I also have a question about my database.

I have a database I've created with Access 2000 (although I may convert it to 2002). This database is split into a front-end and back-end, however, only one user will ever be using it at a time. I recently discovered that in one day the back-end had grown from about 1MB to over 230MB. I opened the back-end, selected Compact On Close, and then exited. This fixed it, and I have not seen any problems since.

What I'm wondering is if the front and back ends are both set to Compact On Close, will the compact them both when the user logs out, or have I just been lucky in avoiding the bloat so far? I am planning to send this off to a customer soon and I want to make sure it works as well as possible before shipment.

Thank you for your help, and please disregard the person information above. Maybe I should give some serious thought to a diet and more exercise...

 

[WildHare]

What I'm wondering is if the front and back ends are both set to Compact On Close, will this compact them both when the user logs out

No. It only compacts the front end. The Back End database isn't actually "opened" by Access, per se - it's just attached by the Front-end Access instance. At least that's what I've deduced must be the situation, because the same thing happens to me (and, I would assume, most everyone with FE/BE applications).

There is code floating around that you can issue to start another instance of Access and load your back end DB, and then close it - this close SHOULD be enough to kick off the compact process.

The problem is, you PROBABLY have to disconnect your links in the F/E database to do it successfully.

I'm sure there is someone watching with a better and more complete solution for you. Let's hope s/he pops in.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[aflat]

Backend compact,

You must disconnect the Front-end completely from the back-end, no forms or tables etc running.
You can still run the code from your Front-end. All you have to do is point to the back-end datafile and compact it "DBEngine.CompactDatabase".

If the back-end file is always the same name you can hard code it, if not you can use a variable string.

Look it up in your Access or VB help files.

aflat

 

[KornGeek]

aflat,
Just to confirm, you don't actually have to disconnect the linked table, just make sure no tables or forms are open, correct?

Also, (please bear with me, my Access help files are not working correctly) is there a way to determine if the compact was successful? I don't want to overwrite my back-end database unless I have a successfully compacted version.

An idea I have (and anybody reading this, feel free to comment) is to rename the back-end from "MyBackend.mdb" to "MyBackend.bak", and then do a compact from "MyBackend.bak" into "MyBackend.mdb". Is this a good/bad/foolish/pointless idea? Any feedback is appreciated.

Thank you very much.

 

[AvGuy]

Create a database named Compact.mdb. Link the autoexec macro of Compact.mdb to a routine that compacts the back end (and/or front end) of your primary application and then closes itself(Compact.mdb).

Then place code in your front end to open Compact.mdb when exiting the primary application.

In essence the routine would "shell out" to another Access mdb to perform the compaction invisibly to the user.

 

[aflat]

Korngeek,

No you don't have to disconnect any linked tables but forms etc. that are connected must be closed.

The routine I use doesn't explicitly check for success of the compact process (it doesn't open the compacted db before renaming it as the original). My routine compacts datafile into a new db named CompTemp.mdb. If successfull, the original datafile is 'Killed' and CompTemp is renamed to the original datafile name. If the routine errors out before CompTemp is created, the original datafile will not be 'killed'.

I have used this type of routine for a few years and the only error that I have encountered has been when CompTemp.mdb doesn't get deleted after being renamed to the old datafile name. Not sure exactly why or when this occurs but the good thing is it never effects the original, it only makes it impossible to compact again as the file "CompTemp.mdb" already exists and routine won't start beacause it's trying to create CompTemp.mdb.

My users and I have used this compact process hundreds of times and have never lost any data.

Back-ups are always good though!

aflat