Creating a Macro in 2010 that creates a whole DB backup

Glenthorne · #1 (**permalink**) 02-02-11, 09:44

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

Sinndho · #2 (**permalink**) 02-02-11, 11:45

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

reddevil1 · #3 (**permalink**) 08-11-11, 05:01

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

Sinndho · #4 (**permalink**) 08-11-11, 11:03

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.

reddevil1 · #5 (**permalink**) 08-25-11, 15:24

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???

Sinndho · #6 (**permalink**) 08-25-11, 16:05

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.

reddevil1 · #7 (**permalink**) 08-26-11, 11:23

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?

Sinndho · #8 (**permalink**) 08-26-11, 13:41

Quote:

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]

Internet Services

Web Development

Digital Marketing

Consumer Tech