Results 1 to 7 of 7
  1. #1
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276

    Compact & Repair Code


    I am trying to Compact Database on Every 5th closing with the help of the following link but have failed to do so.


    http://datapigtechnologies.com/flash...ctonclose.html


    I am having a problem with the following Function. I presume the following code is for access 2000 whereas I am using access 2007


    Function CompactFunction ()
    CommandBars("Menu Bar"). _
    Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Compact and repair database..."). _
    accDoDefaultAction
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    Thx June Its a very helpful link.

    I have made a progress bar which I want to link with the following code.

    This Code Compacts & Repair Database


    Private Sub Command463_Click()

    Dim control As Office.CommandBarControl
    Set control = CommandBars.FindControl(Id:=2071)
    control.accDoDefaultAction

    End Sub

    This Code is For Progress Bar


    Private Sub Command98_Click()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Query1", acViewNormal
    Me.LBL1.Visible = True
    Me.LBL2.Visible = True
    Me.PROGRESS.Caption = "20%"
    DoEvents
    Me.LBL3.Visible = True
    Me.LBL4.Visible = True
    Me.PROGRESS.Caption = "40%"
    DoEvents
    Me.LBL5.Visible = True
    Me.LBL6.Visible = True
    Me.PROGRESS.Caption = "60%"
    DoEvents
    Me.LBL7.Visible = True
    Me.LBL8.Visible = True
    Me.PROGRESS.Caption = "80%"
    DoEvents
    Me.LBL9.Visible = True
    Me.LBL10.Visible = True
    Me.PROGRESS.Caption = "100%"
    DoEvents
    DoCmd.SetWarnings True

    End Sub



    How can I merge the Compact & Repair Code With Progress Bar Code, So when I click the button progress bar runs while Database is compacting and repairing.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Progress bar also a topic of previous threads. Here is one https://www.accessforums.net/program...vba-45744.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    I have posted two codes "Code Compacts & Repair Database" & "Code is For Progress Bar"

    In Code for Progress Bar "DoCmd.OpenQuery "Query1", acViewNormal" trigger the Query1
    What I want is to replace this somehow with

    Dim control As Office.CommandBarControl
    Set control = CommandBars.FindControl(Id:=2071)
    control.accDoDefaultAction

    This I don't know how to do it, Can you help me here.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never done anything like this. All I can do is refer you to other discussions.

    I don't know that this can even be done for the reasons presented in those discussions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    aamer is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Location
    Pakistan
    Posts
    276
    Finally I have made the Compact Database on Every 5th closing with a glitch that I am unable to fix.
    And I need some help here to fix it, I guess it will be a piece of cake for others but it is like climbing K2 for me.

    I have made the following
    1. Table : CompactCount
    2. Module: CompactRoutine
    3. Form: SplashScreen

    Problems I am facing are as follows:
    I have to close startup screen manually 5 time then it triggers compact and repair.
    If I exit the Database it does not compact and repair.
    Splash Screen must start & minimize , Which it does. I want it to be hidden and work in the background.
    There is a Form "PleaseWait" in this database if this could be tied to the process.

    your help is much appreciated.


    I have attached a sample DB.

    SampleCompactDB.zip

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact and repair
    By data808 in forum Access
    Replies: 4
    Last Post: 03-30-2014, 03:22 AM
  2. Compact and Repair
    By Cyberice in forum Access
    Replies: 3
    Last Post: 03-18-2014, 03:22 PM
  3. Replies: 7
    Last Post: 11-22-2013, 07:32 PM
  4. Compact & Repair, Will I Run Into Problems?
    By robsworld78 in forum Access
    Replies: 1
    Last Post: 01-10-2012, 05:11 PM
  5. Compact and repair on close
    By colotazzman in forum Access
    Replies: 2
    Last Post: 05-20-2010, 02:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums