"Lumpierbritches" <lu*************@aol.com> wrote in message
news:20***************************@mb-m21.aol.com...
But HOW? Is what I'm asking.
Well, here's how I do it, especially with customers 12,000 miles away. Some
in this NG argue (vociferously) that such changes result from design/program
incompetence. And that is sometimes true. But if a business is not
developing it is collapsing; change is part of the deal.
As part of the startup checking, and after the data file has been verified,
this code runs -
("dlg" and "errdlg" are part of my message/log system, using a table for the
messages.)
'check fixes
lngCurrVersion = SysVal.DataVersion
Select Case lngCurrVersion
Case cbDataVersion
Case Is > cbDataVersion
dlg 10
GoTo procexit
Case Is < lngFirstVersion
dlg 8
GoTo procexit
Case Else
If Fixit(lngCurrVersion) = False Then
dlg 30
GoTo procexit
End If
End Select
IniOK = True
It gets current datafile version from sysrec table, and compares it
matches the dataversion this Front End db is designed to run with
(cbDataVersion), then skip
greater than cbDataVersion, then FE is old and should be updated
is too early and BE should not be used
else calls Fixit
Public Function Fixit(lngCurrVersion As Long) As Boolean
'^^^^^^^^^^^^^^^^^^^^^^^^^
'called from IniVal
Dim bStatus As Boolean
On Error GoTo procerr
bStatus = False
If OpenFixes() = False Then
GoTo procexit
End If
'insert new fix here, plus update cbDataVersion in GlobalStuff
If lngCurrVersion < 20040419 Then fix20040419
If lngCurrVersion < 20040426 Then fix20040426
If lngCurrVersion < 20040427 Then fix20040427
SysVal.SetVals
bStatus = True
procexit:
On Error Resume Next
CloseFixes
Fixit = bStatus
Exit Function
procerr:
errdlg "Fixit", Err.Number, Err.Description
Resume procexit
End Function
It will run as many fixes as needed.
App can have multiple data files - eg another company, test system, restored
from BU, etc. So more than one BE may need updating over time.
Private Function fix20040428() As Boolean
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Const fixno As String = "20040428"
Dim strSql As String
On Error GoTo procerr
'1
AddField "Repairs", "CustDownAmt", "MONEY"
'2
strSql = "UPDATE CustDown INNER JOIN Repairs ON CustDown.ID =
Repairs.RepairID SET Repairs.CustDownAmt = [DownAmt];"
IniVal.ProgDb.Execute strSql
'3 remove 0/null terms
strSql = "DELETE FROM Financing WHERE ((Financing.FinanceAmt=0 Or
Financing.FinanceAmt Is Null));"
IniVal.DataDb.Execute strSql
procexit:
strSql = "UPDATE Sysrec SET Sysrec.DataVersion = " & fixno & ";"
IniVal.DataDb.Execute strSql
fix20040428 = True
Exit Function
procerr:
errdlg "Fix" & fixno, Err.Number, Err.Description
Resume Next
End Function
The above sample proc adds a field to a table (proc not shown), fixes two
data problems, then updates the data version of the BE.
This ensures only the first in will attempt the update.
I have not used .Execute ... dbFailOnError as I want it to continue. The
messaging and logging alert me to the need to deal urgently with the problem
that arose, and I do not want every startup to run into the same error - so
the BE data version number is updated anyway.
(In 2, I included a short-term FE table holding 20,000 values to update the
new field, and because it was in the FE, I had to use the ProgDb rather than
the DataDb used in preference.This was 2 days after installation. Messy.)
The following are just housekeeping.
Private Function OpenFixes() As Boolean
'^^^^^^^^^^^^^^^^^^^^^^
Dim bStatus As Boolean
On Error GoTo procerr
bStatus = False
DoCmd.Hourglass True
DoCmd.OpenForm "FixitMsg", acNormal, , , , acWindowNormal
SysCmd acSysCmdSetStatus, "Applying updates to data tables..."
bStatus = True
procexit:
On Error Resume Next
OpenFixes = bStatus
Exit Function
procerr:
errdlg "OpenFixes", Err.Number, Err.Description
Resume procexit
End Function
Private Sub CloseFixes()
'^^^^^^^^^^^^^^^^^^^^^^
On Error Resume Next
DoCmd.Close acForm, "FixitMsg"
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False
procexit:
Exit Sub
End Sub
