none
Compact and repair question RRS feed

  • General discussion

  • I have a Access 2013 Front End, Back end configuration.  How do I cause the back end to utilize the Compact on close feature.  Maybe is does that even though I never actually open it per se, and I don't even know it.  Also when you have that feature enabled, does it also do a repair?

    Tuesday, July 23, 2019 1:43 PM

All replies

  • Compact on Close occurs when the database is closed after having been opened in the Access user interface. The backend isn't compacted when the user closes a frontend connected to that backend, because that doesn't open the backend in the Access user interface.

    You can write code to compact the backend. You'd have to run it at a time that nobody is using a frontend that connects to the backend. A compact always implies a repair, but of course in most situations there is nothing to repair.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 23, 2019 1:54 PM
  • Thanks for your response. That is what I thought, but wanted to be sure.  

    I was going to mark it as the answer, but don't see that as an option on your post.

    Do you think I could write code in the front end that would compact and repair the back end when the last user of the front end exited?  If so could you suggest a method?

    Tuesday, July 23, 2019 2:37 PM
  • I assume that you have tables in the frontend linked to the backend. That means that the backend will be in use as long as anyone has the frontend open. So you cannot use code from the frontend to compact the backend. You will have to use a separate database to perform the compact and repair from (or open the backend directly in the Access user interface).

    Here is sample code to compact a database:

    Sub CompactBackend()
        Dim strName As String
        Dim strTemp As String
        On Error GoTo ErrHandler
        Screen.MousePointer = 11 ' hourglass
        ' Path to backend
        strName = "H:\Access\MyBackend.accdb"
        strTemp = Replace(strName, ".accdb", "_temp.accdb")
        ' Create a compacted database
        CompactDatabase strName, strTemp
        If Dir(strTemp) = "" Then
            ' This shouldn't occur
            MsgBox "Failed to create backend", vbExclamation
        Else
            ' Delete the original database
            Kill strName
            ' Rename the compacted database
            Name strTemp As strName
        End If
    ExitHandler:
        Screen.MousePointer = 0 ' default
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    PS You can't mark any reply as answer since you started this topic as a general discussion, not as a question.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 23, 2019 2:54 PM
  • I would not recommend using code to compact the BE.

    Compacting on a network file:

    1. is unstable
    2. takes a long time
    3. shouldn't be done while users might be in the FE editing data.

    I strongly suggest you do the compacting manually by copying the BE to your local drive and compacting it in an Access session. You don't have to do it often unless the tables do a lot of batch deletions or updates. Most of mine only need it once a month or so.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, July 23, 2019 3:27 PM