Automatic backups of tables using ADO 2

[N2Life]

Here are routines that 1) create a text file to hold the current date, 2) create the necessary backup databases to hold the tables, and 3) read the text file to see if the date stored there is current date or not; if it isn't, makes backups automatically (using ADO), then updates the text file to current date. The first 2 routines should be run only once. This approach provides extra insurance, especially if you are using a file server and put the backups (as well as a full copy of the original database) on your C:drive.


Public Sub CreateBackupTextFile()
' This sub presupposes the existence of a folder named BACKUPS, located in the directory in
' which this database is stored. It creates a text file there; the file will be used to
' determine whether daily backups of tables in this database have been made.
' This sub should be run only ONCE.

Dim DataPath As String
Dim ThisString As Variant
Dim ThisFile As String
Dim WhereFile As String
Dim TheDay As String
Dim FileNamePattern As String

' ----- Insert correct file name pattern here -------------------------------------------------
FileNamePattern = "MyDatabaseName_Backup_"

DataPath = Application.CurrentProject.Path & "\BACKUPS\"

TheDay = Day(Date)
If Len(TheDay) = 1 Then
TheDay = "0" & TheDay
End If

WhereFile = DataPath & FileNamePattern & TheDay & ".mdb"
ThisFile = DataPath & "\KeepThis_" & FileNamePattern & "File.txt"

' Write today's date to the file.
Open ThisFile For Output As #1
Write #1, Date
Close #1
MsgBox "The special text file required for backups has been created." & _
vbCrLf & vbCrLf & "It is named: " & ThisFile, vbInformation

End Sub


Public Sub CreateBackupDatabasesADO()
' This sub presupposes the existence of a folder named BACKUPS, located in the directory in
' which this database is stored.
' It creates 31 databases to be used to store daily backups of tables from this database.
' This sub should be run only ONCE.

Dim Ktr As Integer
Dim WhereFile As String
Dim TheDay As String
Dim DataPath As String
Dim FileNamePattern As String
Dim NameOfDB As String
Dim newDB As ADOX.Catalog

' ----- Insert correct file name pattern here -------------------------------------------------
FileNamePattern = "MyDatabaseName_Backup_"

DataPath = Application.CurrentProject.Path & "\BACKUPS\"
WhereFile = DataPath & FileNamePattern
Set newDB = New ADOX.Catalog

For Ktr = 1 To 31
'Adjust the day to 2 characters
TheDay = Trim(CStr(Ktr))
If Len(TheDay) = 1 Then
TheDay = "0" & TheDay
End If

'Define the location and name of the new database
NameOfDB = WhereFile & TheDay & ".mdb"

'Create the database
newDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NameOfDB
Next Ktr

Set newDB = Nothing

MsgBox "All databases have been prepared as " & WhereFile & "nn."
End Sub


Public Sub MakeBackupsADO(FileNamePattern As String)
' This subroutine presupposes the existence of 31 Access databases stored in a folder named BACKUPS
' in the same subdirectory where this database is stored.
' It also presupposes the existence of a text file (Example: KeepThis_MyDatabaseName_Backup_File.txt)
' stored in the BACKUPS folder.
' Every day, when this routine is first called, there is an automatic transfer of the data from all
' the tables into the backup database for that day of the month (example, MyDatabaseName_Backup_15.mdb).
' This routine should be called, for example, when the main form is loaded. An example:
' Private Sub Form_Load()
' DoCmd.Maximize
' Call MakeBackupsADO(FileNamePattern:="MyDatabaseName_Backup_")
' End Sub

Dim DataPath As String
Dim ThisString As Variant
Dim ThisFile As String
Dim WhereFile As String
Dim TheDay As String
Dim mySQL As String
Dim cat As New ADOX.Catalog
Dim tbl As Table
Set cat.ActiveConnection = CurrentProject.Connection

DataPath = Application.CurrentProject.Path & "\BACKUPS\"

' Capture day of month of today's date
TheDay = Day(Date)
' Make it 2 characters
If Len(TheDay) = 1 Then
TheDay = "0" & TheDay
End If

' Get the date currently stored in the special text file
WhereFile = DataPath & FileNamePattern & TheDay & ".mdb"
ThisFile = DataPath & "\KeepThis_" & FileNamePattern & "File.txt"
Open ThisFile For Input As #1
Input #1, ThisString
Close #1

'If date on file is not today's date, then no backup has been made today.
If Date <> CVDate(ThisString) Then
MsgBox "It is time to backup all the tables." & vbCrLf & vbCrLf & _
"When you click on OK, this will happen. You will receive a message once " & _
"this activity is complete.", vbInformation DoCmd.SetWarnings False
For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
mySQL = "SELECT * INTO " & tbl.Name & " IN '" & _
WhereFile & "' from " & tbl.Name
DoCmd.RunSQL mySQL
End If
Next

' Write today's date to the text file. If the form that called this routine is opened again later today,
' nothing will happen.
Open ThisFile For Output As #1
Write #1, Date
Close #1
MsgBox "Today's backups have been made.", vbInformation
End If
End Sub

 

[PROXI]

so with this code that you have above. If i put that in a module and call it where you say to, this will backup all of the tables in my database on a daily basis. If so, will this work over a network too?

Thanks,

PROXI

 

[N2Life]

Hello PROXI,

Yes. Several of my customers use databases on networks, with the database stored on a common drive. Whoever opens the database first each day automatically triggers a check of the text file I mention. By the way, you could keep the reference date in a table within the database. But the text file is very simple; a potential shortcoming, however, is that someone might delete it. This backs up only the data and nothing else, not even the keys or indexes associated with the database, so it is important to maintain somewhere else a full copy of the program, which of course does not have to be updated that often. The main utility of the daily backups is that they allow you, when something has gone wrong, to look back at the evolution of the data prior to the incident.

N2Life

 

[PROXI]

So, If i have BE/ FE application and want to use this, should I make a folder called "Backup" on the server where the Be is of on the server where the FE application is?

Thanks,

PROXI

 

[N2Life]

PROXI,

I suggest you open your database and go to the Immediate Window. Type the following:

? Application.CurrentProject.Path

This will tell you where my code will look for the \Backups folder. Of course, you can always change the code to put the backups anywhere you want.

N2Life

 

[PROXI]

Ok.. 2 things,
1. I can't find out how to edit the code to put the file out on on of my servers. I want to put it out on the "\\eausrv01\eau01a11\shared\"

2. I keep coming up with an error when I get to the section below:

Public Sub CreateBackupDatabasesADO()
' This sub presupposes the existence of a folder named BACKUPS, located in the directory in
' which this database is stored.
' It creates 31 databases to be used to store daily backups of tables from this database.
' This sub should be run only ONCE.

Dim Ktr As Integer
Dim WhereFile As String
Dim TheDay As String
Dim DataPath As String
Dim FileNamePattern As String
Dim NameOfDB As String
[b]Dim newDB As ADOX.Catalog[/b]

how do I fix this prob?

Thanks,

PROXI

 

[PROXI]

ok... I know that this is prob getting old for ya.. but another prob. I just replaced the "datapath" variable with my drive path, and put in a reference that fixed the other problem. Now the next thing.

When it goes to do the backup. It makes 31 copies of blank databases. What is it doing and how do i fix it. I thought that maybe they had to be there and then they just got filled in or something... but if I try to do the backup again, it tells me that the file already exists. How can I fix this?

Thanks,

PROXI

 

[N2Life]

PROXI,

The CreateBackupDatabasesADO routine should be run only once. It does create 31 blank databases which will be used to hold the tables that are stored into one of these each day.

Have another look at the discussion at the start of the MakeBackupsADO routine about how to call it.

I hope this helps.

N2Life

 

[PROXI]

ok... I apologize. I think that I am at the last leg of this adventure. I keep getting hung up on this part:

' Get the date currently stored in the special text file
WhereFile = DataPath & FileNamePattern & TheDay & ".mdb"
ThisFile = DataPath & "\KeepThis_" & FileNamePattern & "File.txt"
Open ThisFile For Input As #1
[b]Input #1, ThisString[/b]
Close #1

It give me a runtime error of 62. How can I fix this one. Thanx for all your help so far.

Thanks,

PROXI

 

[N2Life]

PROXI,

I assume you ran the CreateBackupTextFile routine to create the little text file. Did you change "MyDatabaseName_Backup_" everywhere in the code to match your own database? You might look in Explorer and make sure the text file was created correctly. All it should contain is: #2004-06-17#. By the way, if you want to force a backup today, just change this to, for example, #2004-06-16#, and save and close the file. (Backups are not made if the date in the text file equals today's date.)

N2Life

 

[PROXI]

alright... It is working now. But it is only backup the local tables. Is there a way to get it to backup linked tables as well. The linked tables are the ones that I really need to save.

Thanks,

PROXI

 

[N2Life]

PROXI,

I'm afraid my usefulness as a resource ends here. I have no experience with linked tables, at least not in making automatic backups of them.

Will have to turn this last question of yours over to the real experts of this forum.

Regards,
N2Life

 

[PROXI]

I figured it out.

If Date <> CVDate(ThisString) Then
    MsgBox "It is time to backup all the tables." & vbCrLf & vbCrLf & _
        "When you click on OK, this will happen.  You will receive a message once " & _
        "this activity is complete.", vbInformation    DoCmd.SetWarnings False
    For Each tbl In cat.Tables
       [red]If tbl.Type = "LINK" Then[/red]
            mySQL = "SELECT * INTO " & tbl.Name & " IN '" & _
                WhereFile & "' from " & tbl.Name
            DoCmd.RunSQL mySQL
        End If
    Next

You had it set to type = "TABLE". The only thing that I can't figure out is how to do both... But if I can just get the link tables... I think that I can live with myself
You are the kewlest guy ever man.

Thanks,

PROXI

Thanks,

PROXI

 

[N2Life]

PROXI,

Thanks for teaching me something. I am an eager learner.

You might try the following to get both kinds of tables:
If tbl.Type = "TABLE" or tbl.Type = "LINK" Then

All the best,
N2Life

 

[PROXI]

Yea... I tried that. Logically it should work.. but it gives a type error. It is like it gets confused bouncing from one connection to the next or something. I don't know. I am not that smart to be able to diagnose that prob.

Thanks,

PROXI

 

[PROXI]

Alright... I got something new for ya... Is there a way to adjust this so that it would do an hourly backup or every few hours do the backup?????

Thanks,

PROXI

 

[N2Life]

PROXI,

I suppose you could do that. Each time you did the backup, would you want to simply replace the current backup database for that date? Or do you want to create many separate backups in the same day? In the first case, the simplest approach would be just to create a button that re-creates that day's backup, bypassing reading of the little text file. You could also use the OnTimer event to make that happen automatically, but I don't care for OnTimer myself. The second case is more complicated than I'd really like to think about.

What would be the purpose of such frequent backups?

 

[PROXI]

We have a prob of corruption. So if I do an hourly backup of the system and just overwrite the file, if the datbase corrupts, then we only have to input the last hours files... not 75 files for the day. Also... this is what management wants

Thanks,

PROXI

 

[PROXI]

I was thinking of maybe a way of changing the format that the little text file has. Instead of putting just the date, have it put the date and the time. Then after 60 min has passed on the next opening of the file it would do another backup. Is this possible???

Thanks,

PROXI

 

[N2Life]

PROXI,

That sounds good to me.

Instead of:
Write #1, Date
you would use:
Write #1, Now
That would produce something like this:
#2004-06-19 05:09:26#

I leave it to you to parse this and pick out the date and time, and program the appropriate action.

Routine corruption of a database is no fun to live with. I'm sure you have tried to find the cause. One thing I have found useful in such an "investigation" is the use of a text file to capture what is happening in the database.

You would use:
Open InvestigationFile For Append As #1

Using "Append" allows repeated additions to the same text file. For example, if you think the corruption is occurring during data entry, and you have a line such as:
rstADO!MyVariable = InputVariable

you can capture that action in parallel in your text file:
Open InvestigationFile For Append As #1
Print #1, "New record---------------"
Print #1, "Time and date: " & Now
Print #1, "MyVariable: " & InputVariable
...
Close #1

You can stick code such as that throughout your program, capturing all kinds of things. With such a file, you can sometimes get a handle on where things are going wrong. Not always; sometimes.

Good luck PROXI!

N2Life