Compact & repair a db on closing

[jays35]

Is there a way to compact and repair a db when you close it?

 

[Lightning]

If you are using Access 2000 or 2002, there is an option to Compact On Close. Set this by selecting [TOOLS|OPTIONS from the menu. Go to the General tab and check the "Compact On Close" check box.

This option doesn't exist in Access 97. You would need to write your own code to do this.

HTH
Lightning

 

[jays35]

What would that code be?

 

[bubba100]

Try looking at Thread 705-516830 it works for me.

 

[jays35]

Where do I access that thread or do a search?

 

[hjgoldstein]

Use this as a Hyperlink to the thread.

Thread705-516830

 

[bubba100]

This is what I use to compact a frontend but with some changes it should work on a back-end.

Create a new Access database. This will be used to compact the other database. I've called it Compact.mdb.

Use the following on your front-end on Exit. This is needed because without it the other database will try and execute before the first is closed.

Dim stAppName As String
stAppName = "MSAccess.exe ""C:\yourpath\Compact.mdb"" "
Call Shell(stAppName, 1)
Docmd.Quit

I am sure there are many ways to write better code than this but it works for me.

Now in the Compact db create a module and insert this,


Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

This sets a timer. I use this to "wait" until the other db closes.

Insert this in OnOpen Event Procedure of the form.
DoCmd.Hourglass True
Const cTime = 2000
Call sSleep(cTime)

Add this in OnCurrent Event Procedure of the form. Notice you will need to show your path(s) and your file names.
Dim Counter As Integer
Dim Check
Dim FS
Dim appAccess As String
DoCmd.Maximize
line1:
Const cTime = 2000
Call sSleep(cTime)

Set FS = Application.FileSearch
With FS
With Application.FileSearch
.NewSearch
.LookIn = "C:\YourPath"
.SearchSubFolders = True
.FileName = "FrontEnd.ldb"
.MatchTextExactly = True
If .Execute > 0 Then GoTo line1:
If .Execute = 0 Then GoTo line2:

End With
line2:
If Dir(&quot;C:\YourPath\BAKBackEnd.mdb&quot <> &quot;&quot; Then _
Kill &quot;C:\YourPath\BAKBackEnd.mdb&quot;
FileCopy &quot;C:\YourPath\BackEnd.mdb&quot;, &quot;C:\YourPath\BAKBackEnd.mdb&quot;
If Dir(&quot;C:\YourPath\BackEnd2.mdb&quot <> &quot;&quot; Then _
Kill &quot;C:\YouPath\BackEnd2.mdb&quot;

DBEngine.CompactDatabase &quot;C:\YourPath\BackEnd.mdb&quot;, &quot;C:\YourPath\BackEnd2.mdb&quot;
FileCopy &quot;C:\YourPath\BackEnd2.mdb&quot;, &quot;C:\YourPath\BackEnd.mdb&quot;
DoCmd.Hourglass False
Access.Application.Quit
End With


When the Compact db opens have it set to open the form this will start the program. When the compacting is finished it will shut down.