Compact and repair back end

[Guest]

How can this be done and under what circumstances?

Cheers

 

[Douglas J. Steele]

The DBEngine object (part of the DAO library) has a CompactDatabase method.
You can run it any time no ones in the back end database (i.e.: check to see
whether the ldb file exists in the same folder of the back end. If it
doesn't, you can compact)

 

[Guest]

How would this method be run?

The DBEngine object (part of the DAO library) has a CompactDatabase method.
You can run it any time no ones in the back end database (i.e.: check to see
whether the ldb file exists in the same folder of the back end. If it
doesn't, you can compact)

 

[Douglas J. Steele]

Sorry, my typo. It's now the CompactRepair method (CompactDatabase was in
older versions). No wonder you couldn't find it in the Help file.
http://msdn2.microsoft.com/en-us/library/aa221162(office.11).aspx

You might also be interested in http://support.microsoft.com/kb/230501

 

[Guest]

Hello Douglas,

I have only just got around to doing this. I have copied the function into a
module so how do I get it to work?

This is new to me.

Cheers

 

[Douglas J. Steele]

You call it like you would any other function.

A common approach would be to have a "Compact" button on a form, and call
the function in that button's Click event, although you could use other
approaches if you so desire.

 

[Guest]

That is what I was thinking but wasn't sure.

Will give it a go!

 

[Guest]

I copied the front and back end and relinked all the tables. I created a
button, called it "CompRep" and inserted this in to the "click" event. I get
an "argument not optional" error

Private Sub CompRep_Click()

RepairDatabase

End Sub




The module is:


Function RepairDatabase(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:
RepairDatabase = False

End Function

 

[Pieter Wijnen]

You have to provide the FromDb & toDb Parameters!
RepairDatabase "C:\MyDb.mdb", "C:\MyDb2.mdb"

HTH

Pieter

 

[Guest]

Ah, right!

I have a problem with the module. Mr Steele referred me to a A2003 page for
the code but I am using A2K.

"Application.CompactRepair" not found

 

[Pieter Wijnen]

Access '97 & 2000:

DAO.DBEngine.RepairDatabase strSource
DAO.DBEngine.CompactDatabase strSource, strDestionation

HTH

Pieter

 

[Guest]

What am I supposed to do with them?

Access '97 & 2000:

DAO.DBEngine.RepairDatabase strSource
DAO.DBEngine.CompactDatabase strSource, strDestionation

HTH

Pieter

 

[Pieter Wijnen]

replace the compactrepair command with them

pieter

 

[Guest]

I now have the following. I have replaced the command and the button is
clicking with no errors but nothing is happening:

Function RepairDatabase(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.

DAO.DBEngine.RepairDatabase strSource
DAO.DBEngine.CompactDatabase strSource, strDestionation

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

' Return False if an error occurs.
error_handler:
RepairDatabase = False

End Function


The event in the button is:


Private Sub CompRep_Click()

RepairDatabase "S:\ASSET DATA SERVICES\CUSTOMER SATISFACTION TEAM\Queries
Database 2007\backup\Queries Database NEW_be.mdb", "S:\ASSET DATA
SERVICES\CUSTOMER SATISFACTION TEAM\Queries Database 2007\backup\Queries
Database COPY NEW_be.mdb"

End Sub

 

[Pieter Wijnen]

You have to make sure no links are active for the old BE (no ldb)
ie closing all forms bound to tables, queries, reports & recordsets

HTH

Pieter

 

[Guest]

I have a main form with a button that opens another form with the button on
it to compact. There is no ldb for the old B.E. file. Still nothing is
happening.

Thanks for the help.