Compact and repair back end

D

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)
 
S

scubadiver

How would this method be run?


Douglas J. Steele said:
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)
Click to expand...
 
S

scubadiver

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
 
D

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.
 
S

scubadiver

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
 
P

Pieter Wijnen

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

HTH

Pieter
 
S

scubadiver

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
 
P

Pieter Wijnen

Access '97 & 2000:

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

HTH

Pieter
 
S

scubadiver

What am I supposed to do with them?


Pieter Wijnen said:
Access '97 & 2000:

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

HTH

Pieter
Click to expand...
 
S

scubadiver

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
 
P

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
 
S

scubadiver

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.
 
Top