How To Backup Access Databases: Learn My 5 Different Methods To Backing Up Your Access Database File
With the release of Microsoft, Access 2019 users were able to back up your databases via the tools and utility section of the software application. Prior versions meant having to take a manual file copy via either Windows Explorer or some other file management systems.
Since these database files are singular files (stored as either .MDB or .ACCDB), taking a copy is very straight forward indeed and the first method quickly covers this option.
How often have you had to rely on restoring a corrupted file or lost data from tables that without such a ‘plan B’ contingency, you were in trouble? The answer to this question is that if you had bothered to back up your files then the ‘law of averages’ suggest you would never have to rely on it in the first place – image if you hadn’t bothered taking a back up, what the law of averages would be saying to you right now!
In this article, I’m going to show you 5 different ways on how to backup Access database files starting with the simple conventional route:
1. Normal feature – In the later versions, you simply call the backup command. Depending on which version you use, this will vary in terms of where you need to navigate. With versions prior to 2019, you will need to go to the Tools menu and under Database Utilities choose the Back Database command and follow the simple steps.Later versions including the current 2019 version are managed via the backstage (File tab or Office button) under Save & Publish. Again, the steps are simple to follow.
2. Exporting objects – In the Navigation Pane or Database Window, you can select and right mouse click on any object type and choose the ‘Export… ‘ option and complete the prompts and file formats as you output.
In the later versions, this can be done on multiple selections too saving some extra time. The tables containing real data will give you the most flexible of output types compared to say a module or macro which in that case you will want to export to another pre-defined database file first.
3. Action queries – There are four Action queries at your disposal and using two types here will deposit data and remove data. The two types are called a ‘Make-Table’ and ‘Delete’ queries where the first will create a new table on the fly (ideally in another pre-defined database file) appending the data to it followed by the second query removing the data from its original source (if required).
The second Action query may not be required if you intend to only copy and not move the data which is what a back should really be designed to do in the first place. You could always consider using the ‘Append’ query instead; another Action query.
4. Macros – If you know anything about macros then you can automate selective backups by wrapping the above Action queries into one procedure call.
There are other macro commands like transferring and exporting data and different versions of this application will have some distinctive actions too. Access 2019 has a better interface making it more user-friendly.
5. VBA code – In an ideal world, programming with VBA (Visual Basic for Application) your database application will give you the full power and control over how to backup and be selective too.
Clever coding allows users to interact and be very selective and attach these routines to various events as well and add other enhancements like compacting and repairing the database along the way.
The above choices will give you total control on how and what to back up if you do not want to take a full copy all the time. You will need to be familiar with VBA in order to apply the last item here.
Robert Morris a Microsoft Office expert has been working in the technology industry from the last 5 year. As a technical expert, he has written technical blogs, manuals, white papers, and reviews for many websites such as office.com/setup