Option Compare Database
Option Explicit

' ============================================================
' : mDatabaseMaintenance
'
' :
'      .
'
'  :
'   SoftClearWorkingData
'
'    :
'   -    ;
'   -    - ;
'   -  ;
'   -    ;
'   -   : , ,   ..
' ============================================================

Public Sub SoftClearWorkingData()

    '    .
    '
    ' :
    '          .
    '
    '    :
    '   - t;
    '   - t;
    '   - ;
    '   - .
    '
    '   :
    '   - t;
    '   - t;
    '   -  ;
    '   -   .

    On Error GoTo ErrorHandler

    Dim userAnswer As VbMsgBoxResult
    Dim userSecondAnswer As VbMsgBoxResult

    userAnswer = MsgBox( _
        "    :" & vbCrLf & vbCrLf & _
        "- ;" & vbCrLf & _
        "-   - ;" & vbCrLf & _
        "-   ;" & vbCrLf & _
        "-     /  ." & vbCrLf & vbCrLf & _
        " ,    ." & vbCrLf & vbCrLf & _
        "?", _
        vbQuestion + vbYesNo + vbDefaultButton2, _
        "   " _
    )

    If userAnswer <> vbYes Then
        Exit Sub
    End If

    userSecondAnswer = MsgBox( _
        "   ." & vbCrLf & vbCrLf & _
        "      Access." & vbCrLf & _
        "      .", _
        vbExclamation + vbYesNo + vbDefaultButton2, _
        " " _
    )

    If userSecondAnswer <> vbYes Then
        Exit Sub
    End If

    Call ExecuteSoftClearWorkingData

    MsgBox "    ." & vbCrLf & vbCrLf & _
           " ." & vbCrLf & _
           "  .", _
           vbInformation, _
           " "

    Exit Sub

ErrorHandler:

    MsgBox "      ." & vbCrLf & _
           ": " & Err.Description, _
           vbCritical, _
           " "

End Sub

Private Sub ExecuteSoftClearWorkingData()

    '     .
    '
    '  :
    '   1.   / .
    '   2.   .
    '   3.    .

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim transactionStarted As Boolean

    transactionStarted = False

    Set ws = DBEngine.Workspaces(0)
    Set db = CurrentDb

    ws.BeginTrans
    transactionStarted = True

    ' ------------------------------------------------------------
    ' 1.    .
    ' ------------------------------------------------------------
    Call DeleteAllRowsIfTableExists(db, "t")

    ' ------------------------------------------------------------
    ' 2.    - .
    ' ------------------------------------------------------------
    Call DeleteAllRowsIfTableExists(db, "t")

    ' ------------------------------------------------------------
    ' 3.   .
    '
    '     f.
    '     .
    '     .
    ' ------------------------------------------------------------
    Call DeleteAllRowsIfTableExists(db, "t")
    Call DeleteAllRowsIfTableExists(db, "t")
    Call DeleteAllRowsIfTableExists(db, "")
    Call DeleteAllRowsIfTableExists(db, "")

    ' ------------------------------------------------------------
    ' 4.   .
    ' ------------------------------------------------------------
    Call ResetVesselStates(db)

    ws.CommitTrans
    transactionStarted = False

ExitPoint:

    On Error Resume Next

    Set db = Nothing
    Set ws = Nothing

    Exit Sub

ErrorHandler:

    On Error Resume Next

    If transactionStarted = True Then
        ws.Rollback
        transactionStarted = False
    End If

    Err.Raise Err.Number, Err.Source, Err.Description

End Sub

Private Sub DeleteAllRowsIfTableExists( _
    ByVal db As DAO.Database, _
    ByVal tableName As String _
)

    '     ,   .
    '
    '       .

    On Error GoTo ErrorHandler

    If TableExists(tableName) = False Then
        Debug.Print "  , : " & tableName
        Exit Sub
    End If

    db.Execute "DELETE FROM [" & tableName & "];", dbFailOnError

    Debug.Print " : " & tableName

    Exit Sub

ErrorHandler:

    Err.Raise Err.Number, Err.Source, _
        "   [" & tableName & "]: " & Err.Description

End Sub

Private Sub ResetVesselStates(ByVal db As DAO.Database)

    '     .
    '
    '  t  .
    '  t  .
    '      .

    On Error GoTo ErrorHandler

    If TableExists("t") = False Then
        Debug.Print " t  ,   ."
        Exit Sub
    End If

    db.Execute _
        "UPDATE [t] SET " & _
        "[] = Null, " & _
        "[] = Null, " & _
        "[()] = 0, " & _
        "[] = False, " & _
        "[] = '', " & _
        "[ ] = Null, " & _
        "[ ] = Null, " & _
        "[  ] = Null, " & _
        "[ ] = False, " & _
        "[  ] = Null;", _
        dbFailOnError

    Debug.Print "  ."

    Exit Sub

ErrorHandler:

    Err.Raise Err.Number, Err.Source, _
        "  t: " & Err.Description

End Sub

Private Function TableExists(ByVal tableName As String) As Boolean

    ' ,      .

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    Set db = CurrentDb

    For Each tdf In db.TableDefs

        If StrComp(tdf.Name, tableName, vbTextCompare) = 0 Then
            TableExists = True
            Exit Function
        End If

    Next tdf

    TableExists = False

    Exit Function

ErrorHandler:

    TableExists = False

End Function