"Compact and Repair Database" Command in a Form

Discussion in 'Microsoft Access Form Coding' started by Michael, Feb 19, 2004.

  1. Michael

    Michael Guest

    I would like to run the "Compact and Repair Database"
    command by clicking a command button on a form. I am using
    MS Access 2000.

    The "DoCmd.RunCommand acCmdCompactDatabase" cannot work
    while running a VB code or macro. Also, I can't find the
    right parameters to use with "DoCmd.DoMenuItem" for that
    command.

    I already created a custom toolbar with this command. But
    this is the top menu. I would like to have it also in the
    main menu, which is a form.

    Can it be done?

    Thanks,

    Michael
     
    Michael, Feb 19, 2004
    #1
    1. Advertisements

  2. You can't compact the current database through code.

    --
    Doug Steele, Microsoft Access MVP

    (No private e-mails, please)



    "Michael" <> wrote in message
    news:131c801c3f730$92bc4a10$...
    > I would like to run the "Compact and Repair Database"
    > command by clicking a command button on a form. I am using
    > MS Access 2000.
    >
    > The "DoCmd.RunCommand acCmdCompactDatabase" cannot work
    > while running a VB code or macro. Also, I can't find the
    > right parameters to use with "DoCmd.DoMenuItem" for that
    > command.
    >
    > I already created a custom toolbar with this command. But
    > this is the top menu. I would like to have it also in the
    > main menu, which is a form.
    >
    > Can it be done?
    >
    > Thanks,
    >
    > Michael
    >
     
    Douglas J. Steele, Feb 19, 2004
    #2
    1. Advertisements

  3. Hi Michael,

    The following worked for me. Try it in a copy and see if it also works for
    you. The code is behind the OnClick event of a commandbutton, in my case the
    button name is CompactDB.

    Private Sub CompactDB_Click()
    On Error GoTo Err_CompactDB_Click

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

    Exit_CompactDB_Click:
    Exit Sub

    Err_CompactDB_Click:
    MsgBox Err.Description
    Resume Exit_CompactDB_Click

    End Sub

    Hope this helps,

    Alp
    "Michael" <> wrote in message
    news:131c801c3f730$92bc4a10$...
    > I would like to run the "Compact and Repair Database"
    > command by clicking a command button on a form. I am using
    > MS Access 2000.
    >
    > The "DoCmd.RunCommand acCmdCompactDatabase" cannot work
    > while running a VB code or macro. Also, I can't find the
    > right parameters to use with "DoCmd.DoMenuItem" for that
    > command.
    >
    > I already created a custom toolbar with this command. But
    > this is the top menu. I would like to have it also in the
    > main menu, which is a form.
    >
    > Can it be done?
    >
    > Thanks,
    >
    > Michael
    >
     
    Alp Bekisoglu, Feb 20, 2004
    #3
  4. Michael

    Michael Guest

    It works!

    Thank you very much!

    Michael



    >-----Original Message-----
    >Hi Michael,
    >
    >The following worked for me. Try it in a copy and see if

    it also works for
    >you. The code is behind the OnClick event of a

    commandbutton, in my case the
    >button name is CompactDB.
    >
    >Private Sub CompactDB_Click()
    >On Error GoTo Err_CompactDB_Click
    >
    > CommandBars("Menu Bar"). _
    > Controls("Tools"). _
    > Controls("Database utilities"). _
    > Controls("Compact and repair database..."). _
    > accDoDefaultAction
    >
    >Exit_CompactDB_Click:
    > Exit Sub
    >
    >Err_CompactDB_Click:
    > MsgBox Err.Description
    > Resume Exit_CompactDB_Click
    >
    >End Sub
    >
    >Hope this helps,
    >
    >Alp
    >"Michael" <> wrote in

    message
    >news:131c801c3f730$92bc4a10$...
    >> I would like to run the "Compact and Repair Database"
    >> command by clicking a command button on a form. I am

    using
    >> MS Access 2000.
    >>
    >> The "DoCmd.RunCommand acCmdCompactDatabase" cannot work
    >> while running a VB code or macro. Also, I can't find the
    >> right parameters to use with "DoCmd.DoMenuItem" for that
    >> command.
    >>
    >> I already created a custom toolbar with this command.

    But
    >> this is the top menu. I would like to have it also in

    the
    >> main menu, which is a form.
    >>
    >> Can it be done?
    >>
    >> Thanks,
    >>
    >> Michael
    >>

    >
    >
    >.
    >
     
    Michael, Feb 20, 2004
    #4
  5. Michael

    Guest Guest

    This code works fine on condition that it is the only line in the OnClick-event of your pushbutton

    If you try to put more commands (like a complete VBA procedure), it won't work
    You get a message "You can't compact the open database while running a macro or Visual Basic code. Instead of using a macro or code, on the Tools menu, point to Database Utilities, and then click Compact/Repair Database.

    I have no explanation for this strange behaviour, I just found out after a lot of testing, so don't ask me why ..

    JMathys
     
    Guest, Mar 10, 2004
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. SueM

    user form or form attached to command button

    SueM, Sep 23, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    348
  2. Chris B

    Error on compact and repair database

    Chris B, Apr 27, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    565
    Guest
    Apr 27, 2004
  3. Guest

    Compact and Repair Database

    Guest, Nov 8, 2004, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    310
    Dirk Goldgar
    Nov 8, 2004
  4. Guest
    Replies:
    0
    Views:
    214
    Guest
    Aug 26, 2005
  5. orbojeff

    Command to pass Unbound Value on Form to Filter Sub Form Query

    orbojeff, Nov 21, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    582
    orbojeff
    Nov 21, 2005
  6. Guest

    cking acct form with details form from command button...

    Guest, Dec 8, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    271
    Guest
    Dec 8, 2005
  7. Guest

    Form/Subform/Command Button to Open Form - Type Mismatch Error

    Guest, Jul 12, 2007, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    1,076
    Guest
    Jul 12, 2007
  8. Jeanette Cunningham

    Re: Temporary Form with 2 Sub form Update Main Form And its Sub form ?

    Jeanette Cunningham, Nov 25, 2009, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    945
    Jeanette Cunningham
    Nov 25, 2009
Loading...