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. Sergey S. · Accepted Answer · 2016-03-01T05:15:57

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

VBA code for creating Access Backup

2 Answers