Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    21

    Unanswered: Problems with Backup and Compact

    I would like to create a button that would allow my users to backup and compact the database (an unsplit Access 2003 mdb). If I have two separate buttons for Backup and Compact, it works. But, if I try and call both within the same event, I get this error:

    You attempted to open a database that is already opened exclusively by user <name> on machine <name>. Try again when the database is available. (Error 3356)

    (I am the only person in the database.)

    The following is the code for two buttons. I've included a comment to show where I was trying to include the back up in the compact event.

    =============================

    Private Sub cmdCompact_Click()

    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "You must be the only user when running the compact procedure. Do you want to continue ?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Compact Database" ' Define title.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
    'Here is where I was trying to call BackupDB
    CompactDB
    'DoCmd.Close "frmAdmin"
    End If

    End Sub

    Public Sub CompactDB()

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

    End Sub

    Private Sub cmdBackup_Click()

    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "You must be the only user when backing up the database. Do you want to continue ?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Back up Database" ' Define title.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
    BackupDB
    End If

    End Sub
    Public Sub BackupDB()

    CommandBars("Menu Bar"). _
    Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Back up database..."). _
    accDoDefaultAction

    End Sub

  2. #2
    Join Date
    Jun 2009
    Posts
    21
    p.s. after I get the error, I am unable to exit the application -- either from Exit button in my application or from the menus.
    p.p.s. This is probably just an error trapping problem, right...
    Last edited by db_girl; 07-10-09 at 14:48.

  3. #3
    Join Date
    May 2009
    Posts
    258
    The CommandBars functions will only work if they are the only code in the function, and if the function is called from the last line of another VB function. So if you want to backup and then compact, you'll have to use an alternative backup method, such as:
    Code:
    FileCopy CurrentProject.FullName, Replace(CurrentProject.FullName, ".mdb", "_" & Format(Date, "yyyy-mm-dd") & ".mdb")
    Regards,

    Ax

  4. #4
    Join Date
    Jun 2009
    Posts
    21
    Thank you!

  5. #5
    Join Date
    May 2009
    Posts
    258
    You're welcome! Keep in mind that using this method, the backed up version of the mdb will not be compacted (normally the backup version is also compacted). You can just use the DBEngine.CompactDatabase after copying the backup, if you want to compact it as well.

    Ax

Posting Permissions

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