VBA code for creating Access Backup

Question

I just want to Create Backup file of access, so users from fronted can press button and a backup is created.

I am trying this code, This makes the copy of database, but also copies my VBA Code. I just want to copy Tables. No forms, No reports.

Is it possible to ZIP it from VBA??

Function fMakeBackup()

Dim Source As String
Dim Target As String
Dim retval As Integer


Source = CurrentDb.Name

Target = "C:\Users\Documents\FileName"
Target = Target & Format(Date, "dd-mm") & "   "
Target = Target & Format(Time, "hh-mm") & ".accdb"

' create the backup
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(Source, Target, True)
Set objFSO = Nothing

End Function

Update 1 Using solution given by @Sergey

I modified code as below

Function fMakeBackup()

Dim Target As String
Target = "C:\Users\adarsh.madrecha\Downloads\Delete "
Target = Target & Format(Date, "dd-mm") & " "
Target = Target & Format(Time, "hh-mm") & ".accdb"

' create the Blank file for copying
Access.DBEngine.CreateDatabase Target, DB_LANG_GENERAL

Dim tdf As TableDef    
For Each tdf In CurrentDb.TableDefs
   DoCmd.CopyObject Target, , acTable, tdf.Name
Next

End Function

When I execute this, a popup is shown

If I click on either on the options, the code gives error.

Runtime error 3024 
Copy object action was canceled

Sergey S. · Accepted Answer · 2016-03-01 05:15:57Z

2

You can copy database objects one-by-one to remote database. For instance for tables use code like this:

DoCmd.CopyObject TargetFileName, "MyLocalTableName", acTable, "MyRemoteTableName"

Target file should be already created.

For coping all local tables to remote database:

Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
    DoCmd.CopyObject TargetFileName, tdf.Name, acTable, tdf.Name
Next

answered Mar 1, 2016 at 5:15

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Adarsh Madrecha Over a year ago

Thanks, But when I run your code, getting Runtime error 3024 Could not find the file . Should I also another line of code before your code ? I am new in VBA Programming.

Sergey S. Over a year ago

As I said "Target file should be already created", so you need to create it manually first or copy from empty template using procedure you already used

Adarsh Madrecha Over a year ago

I created a blank access database file using Access.DBEngine.CreateDatabase Target, DB_LANG_GENERAL But still getting Runtime error 3024 Copy object action was canceled

Adarsh Madrecha Over a year ago

Not able to add new line in comments, hence making update 1 to the above question.

Harun24hr Over a year ago

So, is there any way copy remote objects to local database?

Destin Richter · Accepted Answer · 2017-10-16 14:04:31Z

1

Access by default will not allow your newly-created target database to be enabled, hence the error you are getting. If you create the target and make sure it's enabled via the Trust Center Settings, you can get past this issue (but it also means you need to rethink how you rename the database by adding the date and time).

answered Oct 16, 2017 at 14:04

Destin Richter

111 bronze badge

Collectives™ on Stack Overflow

VBA code for creating Access Backup

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related