4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.

com
y( )
Tables (10)
VBA Functions (23)

Ads by
Send feed
Write More Effectively
Why this a

Grammarly for Windows and Mac works where yo

your most important writing. Install now.

Grammarly In

Select Language

HOME HOWTO VBA FUNCTIONS VIDEO TUTORIALS DOWNLOAD CONTACT US LOG IN Recent
  Posts

How to Add Group Head

How to Create Auto-Backup Database Understanding MS Acces

 August 10, 2020  TGoldenEye How to Use a Report Wi

How to Use a Report De
How to create a Query f
How to Use Join Propert
How to create a Simple L
Login Form
How to Display Yes or N
Field
How to Display the Tota
One Query
How to Create a Split Fo

Categories

Access Macro (9)

How to Create Auto-Backup Database Field Format (4)
In this How To, I will demonstrate how to automatically create a backup file of your Forms (49)
database. I will use a copy method that will copy the current file to a new location. We can
Combo box (9)
set it to backup the file when the database file is opened or closed.
List box (5)
Search Form (1)

Function (17)
Only 5 Mins to recover Data
MS Access & SQL (11)
Super easy to use. Download and Recover it! MS Excel VBA How To (7
Other (22)
Queries (11)

Recoverit Download Recordset (3)

Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 1/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Create Function Tables (10)
Send feedback Why this ad?
Under the Create Menu, click the Module icon VBA Functions (23)

Ads by
Send feed

Why this a

The Microsoft Visual Basic application will open

Select Language

Recent Posts

How to Add Group Head

Name the function as CreateBackup() Understanding MS Acces
Enter the VB code shown below inside the function How to Use a Report Wi
Save the Module as fBackup after you are done How to Use a Report De
How to create a Query f
How to Use Join Propert
How to create a Simple L
Login Form
How to Display Yes or N
Field
How to Display the Tota
One Query
How to Create a Split Fo

Categories

Access Macro (9)

Full Code: Field Format (4)
Forms (49)
Public Function CreateBackup() As Boolean Combo box (9)
Dim Source As String
Dim Target As String List box (5)
Dim a As Integer Search Form (1)
Dim objFSO As Object
Dim Path As String Function (17)
Path = CurrentProject.Path 'get location of current folder
MS Access & SQL (11)
Source = CurrentDb.Name
Target = Path & "\BackupDB " MS Excel VBA How To (7
Target = Target & Format(Now(), "mm-dd") & ".accdb" Other (22)

' create the backup Queries (11)

a = 0 Recordset (3)
Set objFSO = CreateObject("Scripting.FileSystemObject")
a = objFSO.CopyFile(Source, Target, True)
Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 2/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Set objFSO = Nothing
Ads by
End Function
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Explanation: Ads by
Path = CurrentProject.Path = the location of the current folder that contains the Send feed
Access database file
Why this a
Source = CurrentDb.Name = the current Access database file
Target = Path & “\BackupDB ” = the new file location to be saved to, and the file
name starts with BackupDB. The backup location is the same as the current file in this
example.
Format(Now(), “mm-dd”) & “.accdb” = name the new file to end with the month-day
(Ex: 08-24) and set the file type to .accdb after BackupDB
Set objFSO = CreateObject(“Scripting.FileSystemObject”) = allow the computer Select Language
system to create a new file
CopyFile(Source, Target, True) = the computer system will copy the current file Recent Posts
(Source) to the assigned location (Target)
How to Add Group Head
The Scripting.FileSystemObject is used to gain an access to a computer’s file system. It can
create new files, folders, directory paths, and access existing ones. The FileSystemObject Understanding MS Acces
has many usable methods such as CopyFile, DeleteFile, CopyFolder etc.
How to Use a Report Wi
More information about FileSystemObject here How to Use a Report De
How to create a Query f
How to Use Join Propert
How to create a Simple L
Login Form
How to Display Yes or N
Field
How to Display the Tota
One Query
How to Create a Split Fo

Apply Function Categories

1.Backup When File Opens Access Macro (9)

We can set the system to make a copy of the current Access database file by calling for the
Field Format (4)
CreateBackup() function. In this example, I set the Navigation Form under the Access
Options from More Commands… of the Customize Quick Access Toolbar to be displayed Forms (49)
after I open this file. See the picture below. Combo box (9)
List box (5)
Search Form (1)

Function (17)
MS Access & SQL (11)
MS Excel VBA How To (7
Other (22)
Queries (11)
Recordset (3)
Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 3/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Ads by
Send feed

Why this a

Select Language

Recent Posts

How to Add Group Head

Understanding MS Acces
How to Use a Report Wi
How to Use a Report De
How to create a Query f
How to Use Join Propert
Under the Form Design View, we need to select the Event Procedure under the On Load How to create a Simple L
Event of the Form property, and then click on three dots (…) next to the Event Procedure. Login Form
How to Display Yes or N
Field
How to Display the Tota
One Query
How to Create a Split Fo

Categories

Access Macro (9)

Field Format (4)
After the Microsoft Visual Basic application opens, you will call for the CreateBackup()
Forms (49)
function under the Fom_Load() procedure, as shown below.
Combo box (9)
List box (5)
Search Form (1)

Function (17)
MS Access & SQL (11)
MS Excel VBA How To (7
Other (22)

When you open the Access database file, the file you set to display under the Access Queries (11)
Options above will open, in this case, the Navigation Form. While the Navigation Form is Recordset (3)
loading, the computer system will make a copy of this file and rename it as specified in the
Reports (12)
CreateBackup() function.
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 4/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Ads by
Send feed

Why this a

After you open the current folder of your current Access file, you will see the backup of Select Language
your Access file with the name, in this example, “BackupDB 08-08”, as shown below.

Recent Posts

How to Add Group Head

Understanding MS Acces
How to Use a Report Wi
How to Use a Report De
How to create a Query f
How to Use Join Propert
How to create a Simple L
Login Form
2.Backup When File Closed
How to Display Yes or N
We can set the system to make a copy of the current Access database file when the Access
Field
program is closed or unloaded. Under the Form Design View, we need to:
How to Display the Tota
In the Form property, select the Event Procedure under the On Close Event or the One Query
On Unload Event
How to Create a Split Fo
Click on the three dots (…) next to the Event Procedure.

Categories

Access Macro (9)

Field Format (4)
Forms (49)
Combo box (9)
List box (5)
Search Form (1)

Function (17)
Call the CreateBackup() function under the Fom_Close() procedure as shown below.
MS Access & SQL (11)
MS Excel VBA How To (7
Other (22)
Queries (11)
Recordset (3)
Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 5/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Ads by
Send feed

Why this a

Select Language

Recent Posts

How to Add Group Head

Understanding MS Acces
Modify Function How to Use a Report Wi
The CreateBackup() function is working perfectly; however, I will show you how to change
How to Use a Report De
the name and location of the backup file. The example below is my original code. It creates
1 backup file per day because the file name ends with the current month and date (08-08 How to create a Query f
for today’s date of August 8, 2020). If you open and close the Access file many times a day, How to Use Join Propert
the backup file will override the old file from that day only. However, if you use the Access
file every day for a year you will still have 365 backup files in your folder. How to create a Simple L
Login Form
VBA Code: How to Display Yes or N
Field

Path = CurrentProject.Path How to Display the Tota

Source = CurrentDb.Name One Query
Target = Path & "\BackupDB "
How to Create a Split Fo
Target = Target & Format(Now(), "mm-dd") & ".accdb"

Categories
Result:
Access Macro (9)
Path = “C:\Users\Tewan\Desktop\How to Create Auto-Backup”
Source = How to Create Auto-Backup DB.accdb Field Format (4)
Target = “C:\Users\Tewan\Desktop\How to Create Auto-Backup\BackupDB “
Forms (49)
Target = “C:\Users\Tewan\Desktop\How to Create Auto-Backup\BackupDB 08-08.accdb”
Combo box (9)
I will illustrate more examples of changing the Path (the destination of backup file) and
List box (5)
Target (the name of new file) in the VB code below.
Search Form (1)
VBA Code:
Function (17)
MS Access & SQL (11)
Path = "C:\TestDB"
Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh") & ".accdb" MS Excel VBA How To (7
Other (22)
Queries (11)
Result:
Recordset (3)
Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB 08-08 12.accdb” (created a backup on August 8, 2020 at Reports (12)
12:15 PM) Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 6/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Code: VBA Functions (23)

Path = "C:\TestDB" Ads by

Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh_mm AM/PM") & ".acc Send feed

Why this a

Result:
Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB 08-09 12_20 PM.accdb” (created a backup on August 9,
2020 at 12:20 PM)

Select Language

VBA Code:
Recent Posts

Path = "C:\TestDB"
Target = Path & "\BackupDB_" & Format(Now(), "mm") & ".accdb"
How to Add Group Head
Understanding MS Acces
How to Use a Report Wi
Result:
How to Use a Report De
Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB_08.accdb” (created a backup on August 9, 2000 at 12:20 How to create a Query f
PM) How to Use Join Propert
How to create a Simple L
Login Form
VBA Code: How to Display Yes or N
Field
Path = "C:\TestDB" How to Display the Tota
Target = Path & "\BackupDB.accdb" One Query
How to Create a Split Fo

Result:
Categories
Path = “C:\TestDB”
Target = “C:\TestDB\BackupDB.accdb” (Save the backup on one file only. The new file will
override the old file) Access Macro (9)
Field Format (4)
You must have a folder called “TestDB” in your computer’s C drive (drive C:) for the Path =
“C:\TestDB”, otherwise you will get an error as shown below. Forms (49)
Combo box (9)
List box (5)
Search Form (1)

Function (17)
MS Access & SQL (11)
MS Excel VBA How To (7
Other (22)
Queries (11)
Recordset (3)
Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 7/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Now we can add more Ads codesby
to test if the assigned folder exists and to create the folder
before creating the backup file. I will use the FileSystemObject method to test or create Tables (10)
Send feedback Why this ad?
the folder inside the If…Statement as shown below. By doing this we won’t get an error. VBA Functions (23)

If objFSO.FolderExists(Path) Then Ads by

a = objFSO.CopyFile(Source, Target, True) Send feed
Else
objFSO.createfolder (Path) Why this a
a = objFSO.CopyFile(Source, Target, True)
End If

You will see the new full code below.

Select Language

Recent Posts

How to Add Group Head

Understanding MS Acces
How to Use a Report Wi
How to Use a Report De
How to create a Query f
How to Use Join Propert
How to create a Simple L
Login Form
Public Function CreateBackup() As Boolean How to Display Yes or N
Dim Source As String Field
Dim Target As String
How to Display the Tota
Dim a As Integer
One Query
Dim objFSO As Object
Dim Path As String How to Create a Split Fo

'Path = CurrentProject.Path 'get location of current folder

Path = "C:\TestDB" Categories
Source = CurrentDb.Name
Target = Path & "\BackupDB " & Format(Now(), "mm-dd hh_MM AM/PM") & ".acc Access Macro (9)
Field Format (4)
'create the backup
a = 0 Forms (49)
Set objFSO = CreateObject("Scripting.FileSystemObject") Combo box (9)
If objFSO.FolderExists(Path) Then ‘Test if the folder exists before maki
a = objFSO.CopyFile(Source, Target, True)
List box (5)
Else Search Form (1)
objFSO.createfolder (Path) ‘Create folder if one does not exist
a = objFSO.CopyFile(Source, Target, True) Function (17)
End If MS Access & SQL (11)
Set objFSO = Nothing
End Function MS Excel VBA How To (7
Other (22)
Queries (11)
Recordset (3)
Related Video: Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 8/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Ads by
Send feed

Why this a

Select Language

Recent Posts

How to Add Group Head

Understanding MS Acces
How to Use a Report Wi
How to Use a Report De
How to create a Query f
 Other  autobackup
How to Use Join Propert

 How to Sort Data Using VB in Excel Part 2 How to Use AutoExec in MS Access  How to create a Simple L
Login Form
How to Display Yes or N
Related posts Field
How to Display the Tota
One Query
How to Create a Split Fo

 January 9, 2022  June 23, 2017  TGoldenEye

 TGoldenEye How to Create a Categories

Notification to Save a
How to create a Simple  August 26, 2017 Change
Login Tracking without a  TGoldenEye Access Macro (9)
Login Form How to Insert Photo into
How to Create a Prompt to
Save a Change With the Field Format (4)
How to create a Simple MS Word Bookmark
Login Tracking without a bound fields on form, Forms (49)
How to Insert Photo into MS
Login Form In this How To, Word Bookmark In this How Microsoft Access... Combo box (9)
Other
I... To, I will demonstrate how... List box (5)
Other Other
Search Form (1)

Function (17)
MS Access & SQL (11)
MS Excel VBA How To (7
Other (22)
Queries (11)
Recordset (3)
Reports (12)
Set Security (5)
https://www.iaccessworld.com/how-to-create-auto-backup-database/ 9/10
4/26/22, 3:36 PM How to Create Auto-Backup Database - iAccessWorld.com
y( )
Ads by
Tables (10)
Send feedback Why this ad?
VBA Functions (23)

Ads by
Send feed

Why this a

Most Recent Video: Thank you for your support

Google
My Youtube
Donate
Terms And Policy

More Videos at YouTube Here

Copyright © 2014-2022. All rights reserved

Proudly powered by WordPress | Theme: SuperMag by Acme Themes

https://www.iaccessworld.com/how-to-create-auto-backup-database/ 10/10