Solved: Compact and Repair Database

[boneKrusher]

Hi all,

Is there a VBA code to Compact and Repair the MS Access Database ?

Thx

[OBP]

Yes, just put compact and repair in the Access VB Editor Help (not the Access help) search field and it will give you an example.

[boneKrusher]

thx

[geekgirlau]

Would you like me to flag this as solved?

[boneKrusher]

yes... Thx

[Imdabaum]

I used the following code from the access help on Compact and repair. However whenever the code is executed it gives an error 7846- Access can't compact and repair the current database.

Can anyone tell me what I'm missing?

This is the code I use to call the repairDB function
stToday = CStr(Date)
stLastCompacted = Me.dteCompact 'Invisible text field linked to tblCompactControl.lastCompact
stDatabaseName = CurrentDb.Name

intDateDiff = DateDiff("m", stLastCompacted, stToday)
If intDateDiff > 4 Then 'Database should be compacted every 4 months.
If MsgBox("System requests that you compact and repair every 4 months?" & vbNewLine & "Compact database?", vbYesNo, _
"Compact Data?") = vbOK Then
'stMessage = SysCmd(acSysCmdSetStatus, "Quarterly Maintenance in Progress.... Please wait")
'Call function to compact and repair database
If RepairDB(CurrentDb.Name, CurrentDb.Name & "_bkup") Then
stSQL = "UPDATE tblCompactControl SET tblCompactControl.lastCompact = #" & _
stToday & "# WHERE (((tblCompactControl.CR_id)=1))"
DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True
End If


Function RepairDB(strSource As String, strDest As String) As Boolean
On Error GoTo NoRepair
DoCmd.Hourglass True
RepairDB = Application.CompactRepair(strSource, strDest, True)
DoCmd.Hourglass False
Repaired:
Exit Function
NoRepair:
Err.Raise Err.Number, , Err.Description
DoCmd.Hourglass False
RepairDB = False
Resume Repaired
End Function

If the repair works then I want the date stored in the tblCompactControl table. Clients want the database to compact every 4 months. Currently they have to call us each time they need to compact and repair the database. Don't know who implemented that rule... but that's the way it was when I got here.

I am testing on a split mdb with linked tables to another mdb, but eventually will be implementing the code in a .mde with linked tables. Will that affect the functionality of the code?

[Imdabaum]

Sorry I figured out what I was doing. You cannot compact and repair the FE of a database from itself due to what C&R entails. I realized it right after I put the post in. I set strSource = [BE.pathname] and strDestination = [Dir where BE resides & "temp.mdb"] Then found this little bit of code that really WOWed me.

Kill strSource
Name strDestination As strSource

That way linked tables are still pointing at the correct path.

[Imdabaum]

If you have a password on the backend it will ask for the password, if you don't want to give out the backend password you cand use this little tid bit instead of the RepairDB = Application.CompactRepair>......

DBEngine.CompactRepair compactDB, bringBackDB, ";pwd= password", , ";pwd= password"
RepairDB= True

[sivasham]

Hi all,

Is there a VBA code to Compact and Repair the MS Access Database ?

Thx


f you want to compact/repair an external mdb file (not the one you are working in just now):

Application.compactRepair sourecFile, destinationFile

If you want to compact the database you are working with:

Application.SetOption "Auto compact", True