Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    14

    Unanswered: Creating a Macro in 2010 that creates a whole DB backup

    Creating macro - so far has resulted in saving each table - but what I would like is a macro that creates a complete DB backup as you can do using File and Options

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Here's a possibility. It will create a copy of the current database with the same name in the same folder but with a .bak extension (an existing copy , if any, will be overwritten):
    Code:
    Sub BackupDatabase()
    
        Dim fs, f
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile(CurrentDb.Name)
        f.Copy Replace(CurrentDb.Name, ".mdb", ".bak"), True
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    In Access 2003, I can back up my Database by simply clicking:-

    File --- Back_Up Database

    Then a pop-up windows asks in which Directory you wish to save the Databse.
    It saves the Database as an .mdb file.

    What code would I need to put into a Macro so that a Button will take the User to the pop-up window??

    Cheers.

    Paul

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Normally it should be (remove the []):
    Code:
    Docmd[.]RunCommand acCmdBackup
    Unfortunately this does not seem to work. Every time I tried to use it, in a VBA procedure or calling its equivalent in a macro, I always received an error message saying that the command was not available (run-time error 2046: The command or action 'Backup' is not available now).

    It should perhaps work when used in a library (.mda add-in), but to me it's not worth the trouble of trying it.
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    I have read that the following code should work......but I get an error message saying:-

    Compile error - argument not optional.
    And the ".CompactDatabase" is highlighted.

    Code:
    Private Sub ButtonName_Click()
    Dim strDBtoBackUp As String
    strDBtoBackUp = "D:My Documents\DBName.mdb"
    DBEngine.CompactDatabase strDBtoBackUp
    End Sub
    Does this work for anyone else???

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    According to the DAO Reference Guide, the syntax is:
    Code:
    DBEngine.CompactDatabase olddb, newdb, locale, options, password
    With:
    - olddb: String identifying the closed database (name and full path) to be compacted.
    - newdb: String identifying the name (name and full path) of the new compacted database. This argument can be the same as olddb.
    - All other arguments are optional.
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Does the old DB have to be CLOSED for this to work?

    It would be great to have a button on a Form which saves the currently OPEN database?

    However, it is easy enough to go to File --- Back_up database. I just find it surprising that there is no code available. I have searched the net and nobody else seems to knowhow to do it either.

    Surprising?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Copies and compacts a closed database, and gives you the option of changing its version, collating order, and encryption. (Microsoft Access workspaces only)
    See:CompactDatabase Method*[Access 2007 Developer Reference]
    Have a nice day!

Tags for this Thread

Posting Permissions

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