Based on MK's TSI_SOON (http://www.trigeminal.com/)I've created a
nifty little procedure that - whenever you compact you db you get an
incremental backup copy.
Given that you have a table with version information you get
incremental backups on a per-version basis.
SEE CODE BELOW
Basic idea is: start TSISOON with the options:
1. "compact this db"
2. Start second db ("AutoCopy.mdb")
3. Parameter for [Autocopy.mdb fnAutoCopy] is Currentdb.name +
currentversion
AutoCopy.mdb will do a filecopy(currentdb.mdb, currentdbV101_01.mdb)
next compact:
AutoCopy.mdb will do a filecopy(currentdb.mdb, currentdbV101_02.mdb)
next version first compact:
AutoCopy.mdb will do a filecopy(currentdb.mdb, currentdbV102_01.mdb)
etc.
AutoCopy.mdb will finish with a farewell call to TSISOON with option:
1. Start first db ("Currentdb.mdb")
And we're back in our currentdb, compacted and well with a
state-of-the-art copy
as bonus.
Only I encounter a small hickup during the farewell call to TSISOON:
an Invalid Pagefault in MSVBM60.DLL brings Access on its knees.
Your valued suggestions please.
Suggestion: you can put the code in the form_open proc of an empty
form; load the form from the tool/menubar.
'----------Code for Current.MDB start
Sub PoorMansVersionControl()
'************************************************* *******
' Purpose:
' ========
' Use this sub to Compact your db and create an incremental
' backup at the same time
'
' Prerequisites
' =============
' Prerequisites: your currentdb and an AutoCopy.mdb
'
' HowItWorks:
' ===========
' Prerequisites: your currentdb and an AutoCopy.mdb
' 1. Pass currentdb.name + currentversion to AutoCopy.mdb
' currentversion is in a table called "tblVersion"
' 2. Pass control to AutoCopy (Using TSISOON by MK)
' 3. AutoCopy.mdb will create incremental copy of currentdb
' (based on scanning the current dir for latest incremental copy)
' 4. AutoCopy.mdb passes control back to (read: opens) currentdb
'
' Remarks:
' ========
' The Currentdb holds a table (tblVersion) with version numbers:
' 1.00 initial version
' 1.01 adjusted layout frmOne
' Each time a compact is done; an incremental copy is also created
' Naming of the incremental backups is (suppose we're on version 1.01)
' CurrentdbV101_01.mdb
' CurrentdbV101_02.mdb
'
'************************************************* ****
' CreatedBy: Willem Pauw, oct 2003
'************************************************* *******
'
'
Dim rs As Recordset
Dim sCurrentDBName As String 'Long name: not 8.3 version
Dim sTableVersion As String
Dim vBackupRoot As Variant 'db-name + tableversion: CurretdbV101
'
'Get Current Versionnumber (1.12, 2.00 etc.)
'
Set rs = CurrentDb.OpenRecordset("SELECT * from tblVersie Order By
Versie desc")
sTableVersion = "V" & Left(rs!versie, 1) & Right(rs!versie, 2) '1.12,
2.01 etc
rs.Close
Set rs = Nothing
'
'Prepare Parameter to pass to AutoCopy.mdb
'
sCurrentDBName = fnLongCurrentDBName
vBackupRoot = Left(sCurrentDBName, Len(sCurrentDBName) - 4) &
sTableVersion
'
'Compact this DB and take a returntrip to AutoCopy.mdb
'
Dim tsd As Object
Set tsd = CreateObject("tsisoon90.connect80")
With tsd
.FileToOpen = "C:\@Projecten\AutoCopy.mdb"
.Exclusive = False
.CompactOld = True
.MakeMDE = False
.FunctionToRun = "fnAutoCopy"
.VariantFunctionArgument = vBackupRoot
.CloseAll Application
End With
Set tsd = Nothing
End Sub
Private Function fnLongCurrentDBName() As String
Dim sFirstLongNameInCurrentDir As String
Dim sShortNameOfCurrentDB As String
Dim sPathOfCurrentDB As String
Dim sTemp As String
Dim cTemp As String * 1
Dim i As Integer
'
' Get a random name in current directory
'
With Application.FileSearch
.NewSearch
.Filename = DBEngine(0)(0).Name
.MatchTextExactly = True
.Execute
sFirstLongNameInCurrentDir = .FoundFiles(1)
End With
Dim intCounter As Integer
'
' Get PathPart of found file
'
For i = Len(sFirstLongNameInCurrentDir) To 1 Step -1
If Mid$(sFirstLongNameInCurrentDir, i, 1) = "\" Then
Exit For
End If
Next i
sPathOfCurrentDB = Left$(sFirstLongNameInCurrentDir, i)
'
' Get DBname-part from currentdb
'
sShortNameOfCurrentDB = CurrentDb.Name
For i = Len(sShortNameOfCurrentDB) To 1 Step -1
cTemp = Mid$(sShortNameOfCurrentDB, i, 1)
If cTemp <> "\" Then
sTemp = cTemp & sTemp
Else
Exit For
End If
Next i
sShortNameOfCurrentDB = sTemp
'
'Let's glue it together
'
fnLongCurrentDBName = sPathOfCurrentDB & sShortNameOfCurrentDB
End Function
'----------Code for Current.MDB end
'----------Code for AUTOCOPY.MDB start
Public Function fnAutoCopy(vDatabasenamePlusTblVersion As Variant)
'************************************************* *******
' Purpose:
' ========
' Based on the passed Parameter this funcion will create
' in incremental copy.
'
' Example
' =======
' Parameter = "CurrentdbV102"
' Files already present: CurrentdbV101_01.mdb and CurrentdbV101_02.mdb
' File created this time: CurrentdbV101_03.mdb
'
' HowItWorks:
' ===========
' Prerequisites: your currentdb and an AutoCopy.mdb (this mdb)
' 1. Pass currentdb.name + currentversion to AutoCopy.mdb
' currentversion is in a table called "tblVersion"
' 2. Pass control to AutoCopy (Using TSISOON by MK)
' 3. AutoCopy.mdb will create incremental copy of currentdb
' (based on scanning the current dir for latest incremental copy)
' 4. AutoCopy.mdb passes control back to (read: opens) currentdb
'
' Remarks:
' ========
' The Currentdb holds a table (tblVersion) with version numbers:
' 1.00 initial version
' 1.01 adjusted layout frmOne
' Each time a compact is done; an incremental copy is also created
' Naming of the incremental backups is (suppose we're on version 1.01)
' CurrentdbV101_01.mdb
' CurrentdbV101_02.mdb
'
'************************************************* ****
' CreatedBy: Willem Pauw, oct 2003
'************************************************* *******
Dim sBackupRoot As String
Dim sDIR As String
Dim iVersionNumberOld As Integer
Dim iVersionNumberCurrent As Integer
Dim iVersionNumberHighest As Integer
Dim sBackupSource As String
Dim sBackupTarget As String
'
'Check for latest backup
'
sBackupRoot = vDatabasenamePlusTblVersion & "_"
sDIR = Dir(sBackupRoot)
'
'Get the latest version
'
Do While Not sDIR = ""
'
'Check highest version
'
iVersionNumberCurrent = Left(Right(sDIR, 6), 2) '.... _01.mdb ...
_02.mdb etc
If iVersionNumberCurrent > iVersionNumberOld Then
iVersionNumberHighest = iVersionNumberCurrent
End If
iVersionNumberCurrent = iVersionNumberOld
'
sDIR = Dir()
Loop
'
'Create backupcopy with new versionnumber
'
sBackupTarget = sBackupRoot & "_" & Right("0" & iVersionNumberHighest
+ 1, 2) & ".mdb"
sBackupSource = Left(sBackupRoot, Len(sBackupRoot) - 5) & ".mdb"
FileCopy Source:=sBackupSource, destination:=sBackupTarget
'
'Now jump back to calling db
'
Dim tsd As Object
Stop
Set tsd = CreateObject("tsisoon90.connect80")
With tsd
.FileToOpen = sBackupSource
.Exclusive = False
.CompactOld = False
.MakeMDE = False
.CloseAll Application
End With
Set tsd = Nothing
End Function
'----------Code for AUTOCOPY.MDB end
