Consulting

Results 1 to 9 of 9

Thread: Solved: Compact and Repair Database

  1. #1

    Solved: Compact and Repair Database

    Hi all,

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

    Thx

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    thx

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Would you like me to flag this as solved?

  5. #5
    yes... Thx

  6. #6
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    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.

  7. #7
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    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.

  8. #8
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    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.

  9. #9
    VBAX Newbie
    Joined
    Mar 2021
    Posts
    1
    Location

    Post Try this code

    Quote Originally Posted by boneKrusher View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •