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

ACC-97 - Great Version Controller/Auto Incremental Backup

P: n/a
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
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.