Cardinal Rule # 1: Back up your database!

[missinglinq]

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!”

[ADezii]

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:

Dim SourceFile, DestinationFile
 
SourceFile = "C:\TestDir\Test.mdb"          ' Define source file name.
DestinationFile = "C:\TestDir\Test2.mdb"    ' Define target file name.
 
FileCopy SourceFile, DestinationFile    ' Copy source to target.

NOTE: There are several Methods you can use to make sure your Backup Filename is unique.

[NeoPa]

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.

[ADezii]

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:

<BaseFileName> & Format$(now(), "ddmmyy_hhmmss") & ".mdb"

Output: <BaseFileName>110207_115737.mdb

[NeoPa]

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 :

<BaseFileName> & Format(Now(), "yyyymmdd_hhmmss") & ".mdb"

[Kevin Wilcox]

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 :

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

[NeoPa]

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

[Kevin Wilcox]

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

[NeoPa]

We're funny we humans aren't we?
If only we behaved logically as we think we do :D

[stanford1608]

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?

[Kevin Wilcox]

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.

Function BackUpDirectory()
Dim MyValue As String
MyValue = "BackUp Directory"
BackUpDirectory = DLookup("[variable]", "tblDbVariables", "[description] = '" & MyValue & "'")
End Function
 
Function BE_FileName()
Dim MyValue As String
MyValue = "BE FileName"
BE_FileName = DLookup("[variable]", "tblDbVariables", "[description] = '" & MyValue & "'")
End Function
 
Function EnableBackups()
Dim MyValue As String
MyValue = "BackUp"
EnableBackups = DLookup("[InForce]", "tblDbVariables", "[Variable] = '" & MyValue & "'")
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.

Function DailyBackup()
On Error GoTo Proc_Err
 
Dim NumberLoggedIn
Dim MyDay
Dim MyMonth
Dim MyYear
Dim MyExpiredBackup
Dim fs
Dim strSaveFile As String
 
Application.Echo False
 
If EnableBackups = -1 Then
    Set fs = CreateObject("Scripting.FileSystemObject")
    strSaveFile = BackUpDirectory & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " Backup " & BE_FileName
    If fs.FileExists(strSaveFile) = True Then
        Set fs = Nothing
        GoTo Proc_Exit
    End If
 
    MyDay = Day(Date - 7)
    MyMonth = Month(Date - 7)
    MyYear = Year(Date - 7)
    MyExpiredBackup = BackUpDirectory & MyDay & "-" & MyMonth & "-" & MyYear & " Backup " & BE_FileName
 
    NumberLoggedIn = DLookup("[MyCount]", "qryLoggedInCount", "[LoggedIn] = -1 ")
 
    If NumberLoggedIn = 1 Then
        fMakeBackup
        If fs.FileExists(MyExpiredBackup) = True Then
            Kill MyExpiredBackup
            Set fs = Nothing
        End If
    End If
End If
 
Proc_Exit:
    Application.Echo True
    Exit Function
 
Proc_Err:
    MsgBox "Oops, that must be error number " & Err.Number & ": " & Err.Description
    Resume Proc_Exit
 
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;

MyDay = Day(Date - 7)
MyMonth = Month(Date - 7)
MyYear = Year(Date - 7)

I’ll be changing this to…..

MyDay = Day(Date - BackUpDays)
MyMonth = Month(Date - BackUpDays)
MyYear = Year(Date - BackUpDays)

Using a function I’ve already written but obviously forgotten to use!

Function BackUpDays() As Long
  Dim MyValue As String
  MyValue = "BackUpDays"
  BackUpDays = DLookup("[variablenumber]", "tblDbVariables", "[variable] = '" & MyValue & "'")
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.

[stanford1608]

oh, can't thank you enought for that. Thats should keep me busy for a while!
Cheers again!

[NeoPa]

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.

[Kevin Wilcox]

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.

[mlcampeau]

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.

[Kevin Wilcox]

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.

Private Sub cboActionID_Enter()
Dim MySQL As String
Dim CurrActionID As Long
CurrActionID = Me.ActionID
Debug.Print CurrActionID
 
End Sub

[mlcampeau]

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.

Private Sub cboActionID_Enter()
Dim MySQL As String
Dim CurrActionID As Long
CurrActionID = Me.ActionID
Debug.Print CurrActionID
 
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

[Kevin Wilcox]

Got it now, thanks

Private Sub cboActionID_Enter()
Dim MySQL As String
Dim CurrActionID As Long
End Sub

[NeoPa]

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

[mlcampeau]

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

[NeoPa]

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

[mlcampeau]

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

[NeoPa]

Quite right - and so it should be :)