CODEWITHSUNDEEP
ms-accessvbadatabase-backups
Adarsh Madrecha
Adarsh Madrecha

Reputation: 7896

VBA code for creating Access Backup

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

Access Security Box

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

Runtime error 3024 
Copy object action was canceled

Upvotes: 0

Views: 13439

Answers (2)

Destin Richter
Destin Richter

Reputation: 11

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).

Upvotes: 1

Sergey S.
Sergey S.

Reputation: 6336

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

Upvotes: 2

Related Questions