Backup Microsoft Access Databases
Using a Microsoft Access database provides a rich set of tools and features that users at all levels tend to chug away entering data, running reports and build new objects and give little time (if any) to some basic housekeeping including the very basics of backing up database files.
There are various strategies and methods that can be applied to your Microsoft Access database and this blog post will highlight some of the options for you.
Backing up your database should be practiced for obvious reasons, but this should also be applied with good failsafe measures too when restoring or recovering lost or corrupted data sets (which can happen from time to time).
Is there a high-level and technical skill required here? Of course not, it just needs a good plan, applying some common sense and implement simple strategies – whether you are a large corporation or small medium sized business!
Take from the following options below and even considered using multiple layered approaches too based on the sensitivity and size of the Access database.
Option 1: Keep Making Local copies of Data. Frequency: Daily
In your server-based system that hosts your Access database, ensure that a server recovery backup process fires up at the end of every working day. A copy of the database file could be written on a different partition or if possible on a secondary hard drive attached to the server. Local backups are simple and should never be missed though this is more of a global strategy and should be beyond the scope of Access files too.
Option 2: Local workstation system level – a complete system backup. Frequency:Weekly
At the end of every working week, say every Friday evening or automated over a weekend, ensure the entire local workstation is backed up in a removable media or an external drive. While this can be a time-consuming process it greatly reduces chances of major data loss, just in case of any hardware error affecting disks. Again, this can be deemed beyond Microsoft Access for other files too.
Options 3: Sign up with a Cloud-based Service Provider for regular remote backups. Frequency: Daily or Weekly (based on volume)
To ensure your data remains protected as far as possible, why not connect to a remote data backup solution provider to ensure a copy of your database is always accessible over the internet. This should be subject to the company policy and the sensitivity of data being held and should ensure you as a business-owner are operating within the scope of the data protection rules for your region (not specified here). Set up the frequency of backup based on the sensitivity of the data. For financial and core business applications, daily backups are suggested. It is critical to note that backing up a large amount of data is a network bandwidth sensitive task and should be performed when other users are not consuming the same bandwidth. Make sure you know the correct restore and recovery procedures from your Service Provider and that you can be selective to the files to be backed up.
Option 4: Microsoft Access Backup tools, Frequency: Daily or Weekly
Take advantage of MS Access’s tools too and back up your database file within the application. Here’s an earlier posting on how to back up your MS Access database the conventional way. You can also (manually or alternate this) take individual object backups of tables using queries, optionally using macros and VBA code. Once type of query called a MAKE-TABLE query being just one of the CATION queries to help facilitate your backups.
There are other options too including the purchase of a dedicated recovery too and programming your own interface but by now, you should be getting the idea here that backups shouldn’t be ignored and it is said…”If you carryout regular backups, you will never require them!”
Why not take a look at some of Microsoft Access eBooks we offer that covers queries and housekeeping tools? All come with a money-back guarantee.
Tags: building microsoft access databases, how to build a microsoft access database, microsoft access database, MS Access, ms access database tutorial
How would you automatically update the accdb frontend if it’s on several clients?
Hi, You can not update other front-ends (if stored locally on their desktops/clients).
The only why to maintain updated versions would be to create a master front-end on your shared server/client and then have a login script to copy down the latest interface based on an version check (perhaps stored in a text file – which gets verified first).
The interface will be sepeafrte from the back-end database itself keeping the data protected and centrally stored.
Thanks Ben, could you give an example of such login script?
I don’t any scripts on file. I would suggest googling examples of scripts for Windows and locate the Copy command using BASIC
Can’t find any examples of login scripts and I don’t know if you’re talking about vbs files or batch files
I will dig intro some archives and see what can be found
I haven’t been able to find old code from the archives.
But this link may help https://social.technet.microsoft.com/Forums/scriptcenter/en-US/eaa8ea0f-f43f-4adb-bf8a-d37b122c31d8/logon-script-copy-delete-files-on-client-pcs?forum=ITCG
Thanks:-)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘ Source file.
strSource = “\\server_name\Testing.txt”
‘ Target location.
strTarget = “C:\”
‘ Copy file.
objFSO.CopyFile strSource, strTarget, True
and then add Microsoft Scripting Runtime to VBA References window but, using this kind of scripting, what methods or properties would you use to do a version check?
In essence, you could just copy down and overwrite the file with your newer version (even if it’s not any newer) to be sure client side versions are the latest in all cases.
To add actual versions, you may need to also copy down a ‘txt’m file containing a version to a reserved folder which is opened and interrogated with the script’s version number using an If test in your script.