Error 7846 Can't compact and repair current database

[Dabaum]

I am trying to create something that will automatically compact and repair
the database after 4 months. Currently the other department has to call
technical support and have a technician click the button to compact and
repair. I am trying to find a way to do this automatically through VBA. I
have heard that too much of compact and repair can be bad so I don't want to
do it every time the application closes. (Correct me if I am wrong).

I have a tabl that stores the date when the compact and repair code is
called. This date is linked to a text field on the splash screen when
opening the database.

If the date in the textbox is greater than 4 months passed the day you open
it then we call the repairDB method.

Function RepairDB(strSource As String, _
strDestination As String) As Boolean
' Input values: the paths and file names of
' the source and destination files.

' Trap for errors.
On Error GoTo error_handler

' Compact and repair the database. Use the return value of
' the CompactRepair method to determine if the file was
' successfully compacted.
RepairDatabase = _
Application.CompactRepair( _
LogFile:=True, _
SourceFile:=strSource, _
DestinationFile:=strDestination)

' Reset the error trap and exit the function.
On Error GoTo 0
Exit Function

' Return False if an error occurs.
error_handler:
Err.Raise Err.Number, , Err.Description
RepairDatabase = False

End Function
Then we update the tbl with the current date that the database was compacted
and repaired.

But every time I get the error saying it can't compact the database. It is
not a multiuser application, but I would like it to be. Any ideas?

 

[Douglas J. Steele]

You're not trying to run this from within the database you're trying to
compact, are you? That's not possible.

If you're using Access 2000 or newer, take a look at
http://www.mvps.org/access/general/gen0041.htm at "The Access Web"

 

[Dabaum]

Function RepairDB(strSource As String, _
strDestination As String) As Boolean
' Input values: the paths and file names of
' the source and destination files.

' Trap for errors.
On Error GoTo error_handler

' Compact and repair the database. Use the return value of
' the CompactRepair method to determine if the file was
' successfully compacted.
RepairDatabase = _
Application.CompactRepair( _
LogFile:=True, _
SourceFile:=strSource, _
DestinationFile:=strDestination)

***********INSERTED ADDITIONAL CODE HERE**********
Kill strSource
Name strDestination As strSource
*******************************

' Reset the error trap and exit the function.
On Error GoTo 0
Exit Function

' Return False if an error occurs.
error_handler:
Err.Raise Err.Number, , Err.Description
RepairDatabase = False

End Function
Then we update the tbl with the current date that the database was compacted
and repaired.

But every time I get the error saying it can't compact the database. It is
not a multiuser application, but I would like it to be. Any ideas?

Sorry spent the day working this out. I just missed the fact that what I
wanted was to compact the data file that the tables were linked to. I
inserted the path name for the BE and got it working great. Just inserted
the directory name for the strDestination + temp.mdb. Then the renaming
keeps the FE tables pointing to the right links.