ted.martin
Registered User.
- Local time
- Today, 21:55
- Joined
- Sep 24, 2004
- Messages
- 743
I have an application where there are several month-end data updates and although the code works fine, I am generally apprehensive that if anything ever went wrong, then the data in the BE tables could partially update and it would be a major problem to unscramble the data.
I have written this code which takes a copy of the BE tables and stores them in a safe place each time one of these major updates occurs.
A key line is to tell the module where the Back-End tables are stored and that is accomplished by this line.
I call the module in the first line of the major update code, with arguments that describe the update action e.g. "b4MonthEnd". The file is dated and for housekeeping purposes will be automatically deleted 7 days after being created.
Here is the code.
I have written this code which takes a copy of the BE tables and stores them in a safe place each time one of these major updates occurs.
A key line is to tell the module where the Back-End tables are stored and that is accomplished by this line.
Code:
strDatamdbPath = CurrentDb.TableDefs("oneofyourbackendtables").ConnectI call the module in the first line of the major update code, with arguments that describe the update action e.g. "b4MonthEnd". The file is dated and for housekeeping purposes will be automatically deleted 7 days after being created.
Here is the code.
Code:
Public Sub BEpath(strAction As String)
DoCmd.Hourglass True
'You can look at the TableDef.Connection property for any of the attached tables.
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim strDatamdbPath, sFile As String
strDatamdbPath = CurrentDb.TableDefs("Advertisers").Connect
' If strDatamdbPath = "" Then 'Local data
' strDatamdbPath = CurrentDb.Name
' Else
strDatamdbPath = Right$(strDatamdbPath, Len(strDatamdbPath) - Len(";DATABASE="))
' End If
fso.CopyFile strDatamdbPath, "C:\temp\" & Format(Date, "dd-mm-yy") & "_BEtables" & strAction & ".accdb", True
' now clean up for more than a week ago
sFile = Dir("c:\temp\*.accdb")
Do While sFile <> vbNullString
If IsDate(Left(sFile, 8)) = False Then GoTo myLoop
If CDate(Left(sFile, 8)) < Date - 7 Then
' Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("c:\temp\" & sFile) = True Then fso.DeleteFile "c:\temp\" & sFile
End If
myLoop:
sFile = Dir
Loop
DoCmd.Hourglass False
MsgBox "A COPY of your BE tables for " & UCase(strAction) & " has been saved.", vbInformation, "BE tables SAVED"
End Sub
Last edited:
