Microsoft Access VBA to Compact and Repair

About this tutorial:

Video duration: 19:49
All Access databases benefit from periodically being compacted and repaired. Being able to compact and repair another database can be beneficial if you need to compact and repair a linked BackEnd database or if you want to compact and repair databases from an Administrative database.

In this video we will:
1. Use a split database as an example application.
2. Use VBA to compact and repair the backend of the split database.

The VBA in this video is located here:

All of my videos organized by topic and other…

Incoming search terms:

Post Author:

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

8 thoughts on “Microsoft Access VBA to Compact and Repair

    Montgomery Cross

    (January 13, 2019 - 1:58 pm)

    I copied and paste the code from the link above. In the first set of codes for Private Sub cmdCompact_Click I modified lines 11 "frmMainMenu" and lines 17 "Parts" to match the form and table in my database. When I try to compile the code I got an error. In line 10 intIndex was not defined I had to include Dim intIndex as Interger.
    The code under CurBackEnd function it claimed k was not defined so I had to add k to the Dim i, j as Interger. After that the code compiled and worked.

    Will M

    (January 13, 2019 - 1:58 pm)

    Great tutorial. I think it might be safer to never delete the source automatically, but instead rename with a time stamp in the name and to compact from that.

    Mahmoud Ezz

    (January 13, 2019 - 1:58 pm)

    Could you help me please ?? I work on my project but i need to make username and password for every user put I don’t know how can i do this and there is another problem that i need to opne the main form in full screen mode except the toolbar to make me able to go to another app that i am opening.. plz help

    Ronnel Caluag

    (January 13, 2019 - 1:58 pm)

    It doesn't work.

    Iconic Analytics

    (January 13, 2019 - 1:58 pm)

    how do i compact the front end database

    Redha Hamad

    (January 13, 2019 - 1:58 pm)

    Your kind help to accurate this Access VBA code to prevent duplicate in multi fields ( Combined together)

    If any of this fields defer then should accept my entry below are the details:  

    ·         My primary key called (DocNo) Number field with DMax function.

    ·         Table Name: tblFacilityRegister

    1.      AccountNo (text field)

    2.      DocumentDate (Date field)

    3.      DocumentName (text field)

    ·         Form Name: frmFacilityDocsRegister

    1.      cboAccountNo (text field)

    2.      txtDocumentDate (Date field)

    3.      txtDocumentName (text field)

    VBA code in AfterUpdate event in txtDocumentName field:

    Private Sub txtDocumentName_AfterUpdate()

    Dim NewAccountNo As String
    Dim NewDocumentName As String
    Dim NewDocumentDate As Date
    Dim stLinkCriteria As String
    Dim DocNo As Integer

    'Assign the entered customer name, Date and  address  to a variable
    NewAccountNo = Me.AccountNo.Value
    NewDocumentDate = Me.DocumentDate.Value
    NewDocumentName = Me.DocumentName.Value

    stLinkCriteria = "[AccountNo] = '" & Me.cboAccountNo & _
    "' And [DocumentDate] = '" & Me.txtDocumentDate & _
    "' And [DocumentName] = '" & Me.txtDocumentName & "'"

    If Me.AccountNo = DLookup("[AccountNo]", "tblFacilityRegister", stLinkCriteria) Then
       MsgBox "This Account, " & NewAccountNo & ", has already been entered in database." _
                  & vbCr & vbCr & "with DocumentDate " & NewDocumentDate & "" _
                  & vbCr & vbCr & "with DocumentName " & NewDocumentName & "" _
                  & vbCr & vbCr & "Press OK to lead you to the previous record.", vbInformation, "Duplicate information"

    'Undo the process and clear all fields   

     Me.Save.Enabled = False
     'Show the record of matched customer name and address from the customer table

     DocNo = DLookup("[DocNo]", "tblFacilityRegister", stLinkCriteria)
         Me.DataEntry = False
         DoCmd.FindRecord DocNo, , , , , acCurrent

    End If
    End Sub

    Deward W Seneh

    (January 13, 2019 - 1:58 pm)

    I have mdb backend file but it keeps giving me error

    janez novak

    (January 13, 2019 - 1:58 pm)

    Great tutorial, thank you for sharing again

Leave a Reply

Your email address will not be published. Required fields are marked *