Error 7846 Can't compact and repair current database

D

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?
 
D

Dabaum

Dabaum said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top