Compacting Linked Tables 1

[planetdrouin]

Does anyone have any suggestions on how to compact the database containing linked tables?

Thanks

Lawrence

 

[gusbrunston]

Yup...

Open the database containing the tables.

Tools -> Database Utilities -> Compact and Repair Database.

Then do the same thing with the database containing the front end. Gus Brunston An old PICKer, using Access2000. Celebrating this year my 72nd birthday and my 50th wedding anniversary.
padregus@attbi.com

 

[planetdrouin]

I should have added more detail. I have a multi-user application in place and won't be on hand to compact the backend myself not is there any person capable of it (don't ask). Is there a way to write code to compact the backend automatically if only person logged on quits or at some preset time?

Lawrence

 

[hsitz]

Go to the Options choice off the Tools menu. I think it is under the 'General' tab that there is a "Compact on close" checkbox. Check it and the database will automatically run the compact and repair operation whenever it is closed. -- Herb

 

[planetdrouin]

I know all about Compact on close in options, but I am talking about the backend. However, the backend is never opened directly, is it, as it is the data tables? Am I missing how easy this is supposed to be?

Lawrence

 

[tlbroadbent]

You can use the CompactDatabase method from a VB module to compact the backend. Check out the following links or search Access help for information on this method.

http://msdn.microsoft.com/library/en-us/office97/xhtml/output/F1/D2/S5A277.asp

http://msdn.microsoft.com/library/en-us/dao360/html/damthcompactdatabase.asp

http://msdn.microsoft.com/library/en-us/dao360/html/damthcompactdatabasex.asp

You can also use the /compact command line option which will cause Access to compact the database referenced on the command line. You can schedule a batch run with the task scheduler in Windows. Have the run start Access with the /compact option. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[DISI]

Try puting running this code when the user exits your application:

Dim dataDB as String

'Assign backend database including its path as dataDB
dataDB = "C:\backend.mdb"
'Compacts backend into a temp database
DBEngine.CompactDatabase dataDB, "C:\temp.mdb"

'Check to see if new databse exists before deleting
'original
If Len(Dir("C:\temp.mdb&quot) > 0 then
Kill dataDB

'Rename temp.mdb to original name
Name "C:\temp.mdb" As dataDB
End If



Hope this is what you need.

Paul
Paul Faculjak
paul@DataIntegritySolutions.com

 

[hsitz]

Lawrence -- Thanks for the clarification. Like an idiot, I had assumed that using compact on close would work whether you were opening the database or just linking tables. Don't ask me why I thought that, I have no idea.

So I'm grateful for the answers provided in this thread and will be making use of them myself. -- Herb

 

[votegop]

Terry:

Your post was excellent. I did not realize how to use Microsoft's Task Scheduler to compact an .mdb (because it wants an .exe file). I now realize I can do something like

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "c:\MyFolder\MyDatabase.mdb" /wrkgrp "c:\MyFolder\MyWorkgroupFile.mdw" /compact

in the Task Scheduler, and it works! I love it.

This will work great with a freeware I use, Peter De Beats' "Compact-A-List", to automatically compact and back-up my back-end. All my back-up and compact problems are now solved.

Your post is worthy of an FAQ

Jay