Automatic compact and repair of backend database

  • Thread starter Thread starter Andrew Smith
  • Start date Start date
A

Andrew Smith

I am looking for a way to automatically compact and repair the backend
database when a user closes the database and nobody else is using it.

So far I have written a function that does the compact and repair, renames
the original file and then renames the compacted file to the name of the
original file. This works fine from the front end database provided all
bound forms are closed, but obviously it does not work if there are any
forms open. So, I wrote another procedure to close all the forms. Then I had
the code on the applications "quit" button run the form close procedure
followed by the compact procedure, but this fails as the .ldb file is still
there until the code stops running, even though all the forms have been
closed.

Is there any way round this, or do I need to look for a different approach?

Thanks in advance.
 
I just happened to write some code to do almost exactly
what you are wanting to do a couple of days ago. In my
database its set to compact the database once a week when
the user clicks the exit button on the switchboard. What
it does is create a batch file on the hard drive which is
then opened via Shell. The batch file waits until the
database locking file .LDB is closed (IE the database is
no longer open) and then opens the database and compacts
it.

Here is the code I used if its of any help.

Public Const selfcompact = "C:\selfcompact.bat"

Public Function SelfCompactUtility()
Dim LockFile As String
Dim nFile As Long
Dim AC As String
AC = Chr$(34)
LockFile = (Left(CurrentDb.Name, Len(CurrentDb.Name) -
3) & "ldb")
If Dir(selfcompact) <> "" Then Kill selfcompact
'Create a batch file to compact current database
nFile = FreeFile
Open selfcompact For Append As nFile
Print #nFile, "Echo Off"
Print #nFile, ":Loop"
Print #nFile, "If Exist " & AC & LockFile & AC & "
Goto Loop"
Print #nFile, "Copy " & AC & CurrentDb.Name & AC
& " " & _
AC & (Left(CurrentDb.Name, Len
(CurrentDb.Name) - 3) & "Old" & AC)
Print #nFile, AC & "C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" & AC & _
" " & AC & CurrentDb.Name & AC & " /compact"
Print #nFile, ":Complete"
Print #nFile, "If Exist " & AC & LockFile & AC & "
Goto Complete"
Print #nFile, "START " & AC & CurrentDb.Name & AC
& " " & AC & CurrentDb.Name & AC
Print #nFile, "del " & AC & selfcompact & AC
Print #nFile, "exit"
Close nFile
SaveCompactDate
Shell selfcompact
DoCmd.Quit
End Function
 
Thanks. I guess I could use that as the basis for an automated compact and
repair of all the back ends.
 
Thanks, I'll have a look at this tomorrow.

Robert Taylor said:
I just happened to write some code to do almost exactly
what you are wanting to do a couple of days ago. In my
database its set to compact the database once a week when
the user clicks the exit button on the switchboard. What
it does is create a batch file on the hard drive which is
then opened via Shell. The batch file waits until the
database locking file .LDB is closed (IE the database is
no longer open) and then opens the database and compacts
it.

Here is the code I used if its of any help.

Public Const selfcompact = "C:\selfcompact.bat"

Public Function SelfCompactUtility()
Dim LockFile As String
Dim nFile As Long
Dim AC As String
AC = Chr$(34)
LockFile = (Left(CurrentDb.Name, Len(CurrentDb.Name) -
3) & "ldb")
If Dir(selfcompact) <> "" Then Kill selfcompact
'Create a batch file to compact current database
nFile = FreeFile
Open selfcompact For Append As nFile
Print #nFile, "Echo Off"
Print #nFile, ":Loop"
Print #nFile, "If Exist " & AC & LockFile & AC & "
Goto Loop"
Print #nFile, "Copy " & AC & CurrentDb.Name & AC
& " " & _
AC & (Left(CurrentDb.Name, Len
(CurrentDb.Name) - 3) & "Old" & AC)
Print #nFile, AC & "C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" & AC & _
" " & AC & CurrentDb.Name & AC & " /compact"
Print #nFile, ":Complete"
Print #nFile, "If Exist " & AC & LockFile & AC & "
Goto Complete"
Print #nFile, "START " & AC & CurrentDb.Name & AC
& " " & AC & CurrentDb.Name & AC
Print #nFile, "del " & AC & selfcompact & AC
Print #nFile, "exit"
Close nFile
SaveCompactDate
Shell selfcompact
DoCmd.Quit
End Function
Click to expand...
 
I've got this sorted now, using my original procedures.

I've changed the code behind the quit button so that it just closes all open
forms and then opens a small unbound form that says "Do you really want to
quit", and has Yes and No command buttons. The code behind the Yes button
runs the routine that compacts the back end (if nobody else has it open) and
then quits the database. The no button just reloads the main form.

This seems to work fine.
 
Back
Top