By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Cardinal Rule # 1: Back up your database!

missinglinq
Expert 2.5K+
P: 3,532
For the life of me I’ve never been able to understand why Access doesn’t have an automatic backup feature, especially given its nasty habit of becoming corrupt! But since it doesn’t, the serious user should develop a strategy of his/her own, because sooner or later you’re going to need that backup! Your db is going to become corrupt or you going to make code modifications that go awry! The worst scenario is that you make modifications and test them out thoroughly, only to find out days or weeks later that the modifications affected a part of your database that you never dreamed it would affect! Backups should be done at least daily during the development stage, and more often if you’re doing a lot of new or innovative work to the database. The following is a scheme that’s served me well; we’ll call the database Acme.mdb

Go into Window Explorer, select your db and then:

Right Click and Click on Copy

Right Click and Click on Paste

Access will place a copy of your db at the bottom of your file listing.

It’ll be called Copy of Acme.mdb

Right Click and Click on Rename

Remove the “Copy of” part, and at the end add the date, so

Copy of Acme.mdb

becomes, for example

Acme02_09_07.mdb

If you make more than one copy a day, add a letter to the end of the date

Acme02_09_07a.mdb, Acme02_09_07b.mdb etc

Now, if you have a problem with a code modification or your db becomes corrupt, you can go back to a specific point in time and pickup again, instead of coming here and crying “Help!”
Feb 9 '07 #1
Share this Question
Share on Google+
22 Replies


ADezii
Expert 5K+
P: 8,597
For the life of me I’ve never been able to understand why Access doesn’t have an automatic backup feature, especially given its nasty habit of becoming corrupt! But since it doesn’t, the serious user should develop a strategy of his/her own, because sooner or later you’re going to need that backup! Your db is going to become corrupt or you going to make code modifications that go awry! The worst scenario is that you make modifications and test them out thoroughly, only to find out days or weeks later that the modifications affected a part of your database that you never dreamed it would affect! Backups should be done at least daily during the development stage, and more often if you’re doing a lot of new or innovative work to the database. The following is a scheme that’s served me well; we’ll call the database Acme.mdb

Go into Window Explorer, select your db and then:

Right Click and Click on Copy

Right Click and Click on Paste

Access will place a copy of your db at the bottom of your file listing.

It’ll be called Copy of Acme.mdb

Right Click and Click on Rename

Remove the “Copy of” part, and at the end add the date, so

Copy of Acme.mdb

becomes, for example

Acme02_09_07.mdb

If you make more than one copy a day, add a letter to the end of the date

Acme02_09_07a.mdb, Acme02_09_07b.mdb etc

Now, if you have a problem with a code modification or your db becomes corrupt, you can go back to a specific point in time and pickup again, instead of coming here and crying “Help!”
On the Close() Event of your Main Form, try this:
Expand|Select|Wrap|Line Numbers
  1. Dim SourceFile, DestinationFile
  2.  
  3. SourceFile = "C:\TestDir\Test.mdb"          ' Define source file name.
  4. DestinationFile = "C:\TestDir\Test2.mdb"    ' Define target file name.
  5.  
  6. FileCopy SourceFile, DestinationFile    ' Copy source to target.
NOTE: There are several Methods you can use to make sure your Backup Filename is unique.
Feb 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
ADezii,
Your code is important to know about, but I don't think the On Close event would be appropriate for creating the backup in the development stage of a project.
Linq,
I like your idea, and will add it to the tutorials section but I would recommend that dates in filenames should always be shown as YYYYMMDD format. Otherwise, in a sequentially ordered file list, the backups will be found in an unhelpful order. Also, if multiple versions are possible in a day, consider using the date format YYYYMMDDHHNN.
Feb 11 '07 #3

ADezii
Expert 5K+
P: 8,597
ADezii,
Your code is important to know about, but I don't think the On Close event would be appropriate for creating the backup in the development stage of a project.
Linq,
I like your idea, and will add it to the tutorials section but I would recommend that dates in filenames should always be shown as YYYYMMDD format. Otherwise, in a sequentially ordered file list, the backups will be found in an unhelpful order. Also, if multiple versions are possible in a day, consider using the date format YYYYMMDDHHNN.
NeoPa:
What I had in mind to guarantee uniqueness was something in the order of:
Expand|Select|Wrap|Line Numbers
  1. <BaseFileName> & Format$(now(), "ddmmyy_hhmmss") & ".mdb"
Output: <BaseFileName>110207_115737.mdb
Feb 11 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
Consider two points.
  1. When viewing a list of your <BaseFileName> files in the directory, they will not show in date order.
  2. Less critical nowadays, but I've been doing this for a while ;), if you have anything from before the millennium a 2-digit date for recent files will sort before those for anything before 2000.
I would recommend using instead :
Expand|Select|Wrap|Line Numbers
  1. <BaseFileName> & Format(Now(), "yyyymmdd_hhmmss") & ".mdb"
Feb 11 '07 #5

P: 68
Consider two points.
  1. When viewing a list of your <BaseFileName> files in the directory, they will not show in date order.
  2. Less critical nowadays, but I've been doing this for a while ;), if you have anything from before the millennium a 2-digit date for recent files will sort before those for anything before 2000.
I would recommend using instead :
Expand|Select|Wrap|Line Numbers
  1. <BaseFileName> & Format(Now(), "yyyymmdd_hhmmss") & ".mdb"
I'm not sure if we're supposed to post in here but I'm generally trying to upload some of the stuff I've learned in the last couple of years as Access can be such a steep hill to climb for newbies, and a thread like this will be useful to some.

For what it's worth, I've just set up a db where backup takes place when the first user of the day logs in; I've opted for this because whilst I can guarantee that a user can only enter the db by using the login form whereas I can't be sure they'll exit from a form - they might just turn off the darned PC (really, they do this). I use a slight variation on some code provided by Dev Ashish (google will take you to his site) and name the files as follows;
strSaveFile = BackUpDirectory & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " Backup " & BE_FileName

- BackUpDirectory is (no surprises here) a string containing the target directory, BE_FileName is of course the filename string.

Within the backup procedure I check whether the file already exists and if it does, I exit the procedure - I only want 1 daily backup. I also delete redundant copies, using a user-defined variable, in other words the db administrator can set the db to keep copies for say 7 days, or 20 days, whatever they want, and automatically delete the oldest copy each time a fresh backup is made. This keeps the HD from getting unnecessarily cluttered.
Sep 25 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
Sure you can post in here Kevin.
I would highlight (again) the problems dealing with files whose name order is different from the date order though. Especially when forming it of date elements. It is naturally confusing for people to sort things out of date order when they are dealing with dates. It's a bit like telling someone to read out words which are colours but in coloured writing that doesn't match the word. Try it sometime to see them struggle. Day - Month - Year in names is similarly confusing IMHO (If the date weren't important after all, why would it be included in the name?).
Sep 25 '07 #7

P: 68
Sure you can post in here Kevin.
I would highlight (again) the problems dealing with files whose name order is different from the date order though. Especially when forming it of date elements. It is naturally confusing for people to sort things out of date order when they are dealing with dates. It's a bit like telling someone to read out words which are colours but in coloured writing that doesn't match the word. Try it sometime to see them struggle. Day - Month - Year in names is similarly confusing IMHO (If the date weren't important after all, why would it be included in the name?).

NeoPa, I now see exactly what you mean and will change my naming accordingly. To be honest I hadn't given a seconds thought to sorting files by name etc, which is odd because when it's me sorting someone else's files I curse and swear at sloppy naming!
Thanks for the advice
Sep 25 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
We're funny we humans aren't we?
If only we behaved logically as we think we do :D
Sep 25 '07 #9

P: 6
I'm not sure if we're supposed to post in here but I'm generally trying to upload some of the stuff I've learned in the last couple of years as Access can be such a steep hill to climb for newbies, and a thread like this will be useful to some.

For what it's worth, I've just set up a db where backup takes place when the first user of the day logs in; I've opted for this because whilst I can guarantee that a user can only enter the db by using the login form whereas I can't be sure they'll exit from a form - they might just turn off the darned PC (really, they do this). I use a slight variation on some code provided by Dev Ashish (google will take you to his site) and name the files as follows;
strSaveFile = BackUpDirectory & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " Backup " & BE_FileName

- BackUpDirectory is (no surprises here) a string containing the target directory, BE_FileName is of course the filename string.

Within the backup procedure I check whether the file already exists and if it does, I exit the procedure - I only want 1 daily backup. I also delete redundant copies, using a user-defined variable, in other words the db administrator can set the db to keep copies for say 7 days, or 20 days, whatever they want, and automatically delete the oldest copy each time a fresh backup is made. This keeps the HD from getting unnecessarily cluttered.
Hi Kevin,

I like your idea for automatic back ups and would like to apply the same method on a project i'm working on. It's my 1st project and I'm still getting to grips with VB code. Is there any chance you could post your code so I can learn how it works?
Sep 26 '07 #10

P: 68
Sure

Firstly, go to http://www.mvps.org/access/api/api0026.htm and copy the code written by Dev Ashish. To respect his copyright (and the enormous amount of his code that I’ve used!) I won’t paste it here.

In fMakeBackup I’ve removed the message box asking the user to confirm as I didn’t want this. I’ve also modified a few lines;

'I also changed …
'strSaveFile = CurrentDb.Name
…to…
strSaveFile = BackUpDirectory & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " Backup " & BE_FileName

and…
'.pFrom = CurrentDb.Name & vbNullChar
'to…
.pFrom = BackUpDirectory & BE_FileName ' & vbNullChar
'…as I wanted to back up my back end, not my front end.

I haven’t yet implemented the change we talked about in the thread (a task for today) but to me the advice was absolutely rock solid.

I have three of my own functions to get the directory and filename,and also to check whether backing up is enabled – this is because I have a table called tblDbVariables in which I store a host of things which the db administrator can alter to suit, such as the directory for the BE, the BE filename, whether to have backing up taking place.
Expand|Select|Wrap|Line Numbers
  1. Function BackUpDirectory()
  2. Dim MyValue As String
  3. MyValue = "BackUp Directory"
  4. BackUpDirectory = DLookup("[variable]", "tblDbVariables", "[description] = '" & MyValue & "'")
  5. End Function
  6.  
  7. Function BE_FileName()
  8. Dim MyValue As String
  9. MyValue = "BE FileName"
  10. BE_FileName = DLookup("[variable]", "tblDbVariables", "[description] = '" & MyValue & "'")
  11. End Function
  12.  
  13. Function EnableBackups()
  14. Dim MyValue As String
  15. MyValue = "BackUp"
  16. EnableBackups = DLookup("[InForce]", "tblDbVariables", "[Variable] = '" & MyValue & "'")
  17. End Function
I then have the following function to execute the backup. NumberLoggedIn is where I check that this is the first user to log in that day. The reason I chose the first user in is because at this stage no-one at all can possibly be editing a record in the core data. In fact whilst writing this I’ve noticed that I should have put the number logged in checking just before the Application.Echo False line, as if this is > 1 I just want to exit the procedure and not bother with the rest.
Expand|Select|Wrap|Line Numbers
  1. Function DailyBackup()
  2. On Error GoTo Proc_Err
  3.  
  4. Dim NumberLoggedIn
  5. Dim MyDay
  6. Dim MyMonth
  7. Dim MyYear
  8. Dim MyExpiredBackup
  9. Dim fs
  10. Dim strSaveFile As String
  11.  
  12. Application.Echo False
  13.  
  14. If EnableBackups = -1 Then
  15.     Set fs = CreateObject("Scripting.FileSystemObject")
  16.     strSaveFile = BackUpDirectory & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " Backup " & BE_FileName
  17.     If fs.FileExists(strSaveFile) = True Then
  18.         Set fs = Nothing
  19.         GoTo Proc_Exit
  20.     End If
  21.  
  22.     MyDay = Day(Date - 7)
  23.     MyMonth = Month(Date - 7)
  24.     MyYear = Year(Date - 7)
  25.     MyExpiredBackup = BackUpDirectory & MyDay & "-" & MyMonth & "-" & MyYear & " Backup " & BE_FileName
  26.  
  27.     NumberLoggedIn = DLookup("[MyCount]", "qryLoggedInCount", "[LoggedIn] = -1 ")
  28.  
  29.     If NumberLoggedIn = 1 Then
  30.         fMakeBackup
  31.         If fs.FileExists(MyExpiredBackup) = True Then
  32.             Kill MyExpiredBackup
  33.             Set fs = Nothing
  34.         End If
  35.     End If
  36. End If
  37.  
  38. Proc_Exit:
  39.     Application.Echo True
  40.     Exit Function
  41.  
  42. Proc_Err:
  43.     MsgBox "Oops, that must be error number " & Err.Number & ": " & Err.Description
  44.     Resume Proc_Exit
  45.  
  46. End Function
As a further postscript, I’ve just noticed that I’ve hard-coded the period for deleting the redundant backups, when in fact I want this to be amendable by the administrator; so whereas I currently have;
Expand|Select|Wrap|Line Numbers
  1. MyDay = Day(Date - 7)
  2. MyMonth = Month(Date - 7)
  3. MyYear = Year(Date - 7)
I’ll be changing this to…..
Expand|Select|Wrap|Line Numbers
  1. MyDay = Day(Date - BackUpDays)
  2. MyMonth = Month(Date - BackUpDays)
  3. MyYear = Year(Date - BackUpDays)
Using a function I’ve already written but obviously forgotten to use!
Expand|Select|Wrap|Line Numbers
  1. Function BackUpDays() As Long
  2.   Dim MyValue As String
  3.   MyValue = "BackUpDays"
  4.   BackUpDays = DLookup("[variablenumber]", "tblDbVariables", "[variable] = '" & MyValue & "'")
  5. End Function
I’ve put everything in here as is’ rather than tweaking the code within the response, as I haven’t checked that the changes to be made work fine, so at least you’ll have the original which I do know works fine.
Sep 26 '07 #11

P: 6
oh, can't thank you enought for that. Thats should keep me busy for a while!
Cheers again!
Sep 26 '07 #12

NeoPa
Expert Mod 15k+
P: 31,186
Sure...
(useful code)
...
Nicely Kevin.
It seems you're going to be a regular visitor (which is great for us) so could I ask that you remember to use the CODE tags feature in your future posts. We have found that code posted in tags is much much easier for everyone to read and understand easily, which is why we require them.
Looking forward to bumping into more of your posts ;)

-NeoPa.
Sep 26 '07 #13

P: 68
Nicely Kevin.
It seems you're going to be a regular visitor (which is great for us) so could I ask that you remember to use the CODE tags feature in your future posts. We have found that code posted in tags is much much easier for everyone to read and understand easily, which is why we require them.
Looking forward to bumping into more of your posts ;)

-NeoPa.
Happy to do so NeoPa, can I clarify though; do you mean I need to put Code: ( text ) before any code section? or do you mean I need to put in all the line numbers too? If it's the latter, is this something I could do automatically within the VBA window, and how?!

I'll probably be a more intermittent visitor than it appears, right now I'm 100% occupied and absorbed with completing a db but I generally just seem to do this kind of thing for a few weeks every one or two years as it's not at all my day job.
Sep 26 '07 #14

Expert 100+
P: 296
Happy to do so NeoPa, can I clarify though; do you mean I need to put Code: ( text ) before any code section? or do you mean I need to put in all the line numbers too? If it's the latter, is this something I could do automatically within the VBA window, and how?!

I'll probably be a more intermittent visitor than it appears, right now I'm 100% occupied and absorbed with completing a db but I generally just seem to do this kind of thing for a few weeks every one or two years as it's not at all my day job.
Kevin,
To wrap your text in code tags, just highlight your code and click on the number sign at the top of the message box. If you are posting vba code, if you could then edit the [code] to look like [code=vba] and if it's sql to look like [code=sql]. The Code tags will add the numbers itself.
Sep 26 '07 #15

P: 68
Kevin,
To wrap your text in code tags, just highlight your code and click on the number sign at the top of the message box. If you are posting vba code, if you could then edit the [code] to look like [code=vba] and if it's sql to look like [code=sql]. The Code tags will add the numbers itself.
Just testing the instruction to see if I've understood, HTML is an absolute mystery to me. And there was me thinking everyone except me was numbering every line in their code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboActionID_Enter()
  2. Dim MySQL As String
  3. Dim CurrActionID As Long
  4. CurrActionID = Me.ActionID
  5. Debug.Print CurrActionID
  6.  
  7. End Sub
Sep 26 '07 #16

Expert 100+
P: 296
Just testing the instruction to see if I've understood, HTML is an absolute mystery to me. And there was me thinking everyone except me was numbering every line in their code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboActionID_Enter()
  2. Dim MySQL As String
  3. Dim CurrActionID As Long
  4. CurrActionID = Me.ActionID
  5. Debug.Print CurrActionID
  6.  
  7. End Sub
Your post didn't show up quite like it should've because when you hit reply it still had my opening code tags without closing tags. But otherwise, you did it correctly! (As you can see in the above quote)

[EDIT] I see you edited your post while I was posting. Keep your closing tag as [/code] and just delete my quote and your code tags will work
Sep 26 '07 #17

P: 68
Got it now, thanks

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboActionID_Enter()
  2. Dim MySQL As String
  3. Dim CurrActionID As Long
  4. End Sub
Sep 26 '07 #18

NeoPa
Expert Mod 15k+
P: 31,186
8-)
Thanks for stepping in Michelle.
I would add that, as the CODE=etc versions are predefined and not quite as we'd have them given fuller control, using them is entirely optional. I prefer not to myself as experience has taught me that they are more of a hindrance than a help, but that is a personal choice and everyone can choose whether or not they prefer those or the vanilla variety.

@Kevin
You step in whenever you have the time and inclination. We'll be here but we don't expect full-time attendance from all members (not even from the experts and mods). Whenever members are around is a bonus really.
BTW If you hadn't noticed - you just hit full member status :)
Sep 27 '07 #19

Expert 100+
P: 296
8-)
Thanks for stepping in Michelle.
I would add that, as the CODE=etc versions are predefined and not quite as we'd have them given fuller control, using them is entirely optional. I prefer not to myself as experience has taught me that they are more of a hindrance than a help, but that is a personal choice and everyone can choose whether or not they prefer those or the vanilla variety.
I happen to be partial to the CODE=etc version just cuz I like the pretty colors ;)
Sep 27 '07 #20

NeoPa
Expert Mod 15k+
P: 31,186
I can't say that's how I approach it ;) but we all have our own preferences.
Actually, even if I did think that way there's no way I'd get away with saying like that :D
Sep 27 '07 #21

Expert 100+
P: 296
I can't say that's how I approach it ;) but we all have our own preferences.
Actually, even if I did think that way there's no way I'd get away with saying like that :D
That's the joys of being a girl! You can get away with most anything! (especially if you bat your eyelashes and make pouty lips) ;)
Sep 27 '07 #22

NeoPa
Expert Mod 15k+
P: 31,186
Quite right - and so it should be :)
Sep 27 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.