Hi all,
Is there a VBA code to Compact and Repair the MS Access Database ?
Thx
Hi all,
Is there a VBA code to Compact and Repair the MS Access Database ?
Thx
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.
thx
Would you like me to flag this as solved?
yes... Thx
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
[vba]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
[/vba]
[vba]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[/vba]
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?
Someday I'll understand everything...
Even then...I'll still pretend I'm a beginner.
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.
Someday I'll understand everything...
Even then...I'll still pretend I'm a beginner.
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>......
[VBA]DBEngine.CompactRepair compactDB, bringBackDB, ";pwd= password", , ";pwd= password"
RepairDB= True[/VBA]
Someday I'll understand everything...
Even then...I'll still pretend I'm a beginner.