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?