Database Bloat on DAO CommitTrans by 400 kb/commit

[angeleumbra]

Hello Everyone,

I have been bashing my head against the keyboard for weeks now about this issue. My database is a non-split database (and unfortunately needs to stay that way for the foreseeable future). There are two tables that matter in this circumstance that I call "Big Daddy" and "Little Sister" tables. This database stores raw data from a water treatment facility and analyzes the data as needed. The Big Daddy table stores all the raw data exactly as it is received from the facility. The Little Sister table stores the analyzed data. On a button click, all of the data in the Little Sister table is deleted, the pertinent pieces of information re-imported from the Big Daddy table and re-evaluated using a few different sub-procedures. On the DelanoFullscaleStatus2 procedure (shown below), several fields are reviewed for specific criteria and modified as needed using RS.edit. Every 1000 records, the transaction is committed and the database grows by 400 kb. With 79000+ records and growing, this takes my normally 45 MB database and bloats it to 335 MB until I use a compact and repair (C&R) to slim it back down. I know C&R's are not the greatest things in the world, but I always have a duplicate and worse case, I can always re-import the raw data to rebuild a corrupted database. Anyone have any ideas as to why the database bloats by 400 kb every time I use wkspDAO.CommitTrans dbForceOSFlush?



Windows 7 x64 bit OS with Access 2013.

Public Sub DelanoFullscaleStatus2(strTableName As String, intUnitNumStatus As Long)
    Dim intRowCounter As Long
    Dim intRowStart As Long
    Dim dteBRStart As Date
    Dim dteBREnd As Date
    Dim dteBFStart As Date
    Dim dteBFStop As Date
    Dim intBRRunCounter As Integer
    Dim intBFRunCounter As Integer
    Dim intBROnlineCounter As Long
    Dim intBFOnlineCounter As Long
    Dim sngRunTime As Single
    Dim blnBRBW As Boolean
    Dim blnBFBW As Boolean
    Dim blnOffline As Boolean
    Dim blnWrite As Boolean
    Dim strWhat As String
    Dim sngUnitVol As Single
    Dim intWhatBW As Integer  'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
    
    On Error GoTo ErrorHandler
    
    'To do this, use a DAO Recordset and workspace, which would drastically improve performance.
    Dim wkspDAO As DAO.Workspace
    
    'Open the database
    Set dbProformance = CurrentDb
    Set wkspDAO = DBEngine.Workspaces(0)
    
    strSQL = "SELECT " & strTableName & "_LS_ref.numVol FROM " & strTableName & "_LS_ref " _
        & "WHERE " & strTableName & "_LS_ref.strLSTableName = '" & strTableName & "_U" & intUnitNumStatus & "';"
    'debug.print strSQL
    
    Set RS = dbProformance.OpenRecordset(strSQL)
        
    If RS.EOF = True And RS.BOF = True Then 'The RS has nothing in it and you need to add the first process/facility.
        'Little sister table is empty.
        MsgBox "There is no data to analyze. Please import data before continuing.", vbOKOnly
        Exit Sub
    Else
        sngUnitVol = RS.Fields(0) * 7.48 'cu. ft. to gallon conversion.
    End If
    
    Set RS = Nothing

    'First task is to open the little sister table and populate a recordset.
    strSQL = "SELECT dteTimeStamp, txtStatus, txtMode, col_U" & intUnitNumStatus & "_Q_EFF, col_U" & intUnitNumStatus & "_Q_IN, " _
        & "col_U" & intUnitNumStatus & "_Q_BW_EFF, col_U" & intUnitNumStatus & "_RT_BR, " _
        & "col_U" & intUnitNumStatus & "_RT_BF, intBRRunCounter, intBFRunCounter, " _
        & "col_U" & intUnitNumStatus & "_EBCT,col_U" & intUnitNumStatus & "_NO3_BlendEff, " _
        & "col_U" & intUnitNumStatus & "_Cl2Resid_ContactPipe, calc_U" & intUnitNumStatus & "_CT_ContactPipe, " _
        & "calc_U" & intUnitNumStatus & "_Vol_BW_EFF, calc_U" & intUnitNumStatus & "_Vol_FTW, " _
        & "calc_U" & intUnitNumStatus & "_Vol_Treated,col_U" & intUnitNumStatus & "_Q_BW_IN, " _
        & "calc_U" & intUnitNumStatus & "_Vol_Dist, calc_U" & intUnitNumStatus & "_Vol_Bypassed, calc_U" & intUnitNumStatus & "_Vol_WellDisc, " _
        & "calc_U" & intUnitNumStatus & "_Vol_BW_IN, calc_U" & intUnitNumStatus & "_CT_Required, col_U" & intUnitNumStatus & "_Temp_ContactPipe, " _
        & "col_U" & intUnitNumStatus & "_Pres_DistSys, col_U" & intUnitNumStatus & "_Pres_BF_Eff " _
        & "FROM " & strTableName & "_U" & intUnitNumStatus & " ORDER BY dteTimeStamp ASC;"
    
    'debug.print strSQL
    
    Set RS = dbProformance.OpenRecordset(strSQL)
        
    If RS.EOF = True And RS.BOF = True Then 'The RS has nothing in it and you need to add the first process/facility.
        'Little sister table is empty.
        MsgBox "There is no data to analyze. Please import data before continuing.", vbOKOnly
        Exit Sub
    Else
        RS.MoveLast
        RS.MoveFirst
        lngRowCount = RS.RecordCount
    End If
    
    'Then evaluate each row in the record set.
    blnBRBW = True  'Assume it backwashed the first run so the program recognizes the first date.
    blnBFBW = True  'Assume it backwashed the first run so the program recognizes the first date.
    intBRRunCounter = 1
    intBFRunCounter = 1
    
    wkspDAO.BeginTrans
    For lngIndex1 = 1 To lngRowCount  'may need to change start to 0 if RS is zero bound.

        With RS
            .Edit
            
            Select Case True
            Case ((.Fields(3).Value > 20) And (.Fields(5).Value = 0) And (.Fields(4).Value <> 0) And (.Fields(4).Value > 20))
                
                'System just came online.
                .Fields(1).Value = "Online"
                blnOffline = False
                
                intRowStart = lngIndex1
                
                If blnBRBW = True And intBROnlineCounter > 623 Then  '52 hours in 5 minute intervals
                    intBROnlineCounter = 0
                    blnBRBW = False
                    dteBRStart = .Fields(0).Value
                ElseIf blnBRBW = True Then
                    intBROnlineCounter = intBROnlineCounter + 1
                    blnBRBW = False
                    dteBRStart = .Fields(0).Value
                Else
                    intBROnlineCounter = intBROnlineCounter + 1
                End If
                
                If blnBFBW = True And intBFOnlineCounter > 864 Then  '72 hours in 5 minute intervals
                    intBFOnlineCounter = 0
                    blnBFBW = False
                    dteBFStart = .Fields(0).Value
                ElseIf blnBFBW = True Then
                    intBFOnlineCounter = intBFOnlineCounter + 1
                    blnBFBW = False
                    dteBFStart = .Fields(0).Value
                Else
                    intBFOnlineCounter = intBFOnlineCounter + 1
                End If
                           
                'Next, I need to determine the mode of operation.
                Select Case .Fields(4).Value >= (1.1 * CSng((.Fields(3).Value) + CSng(.Fields(17).Value)))
                Case True
                
                    If Len(.Fields(25).Value) > 0 Then
                        Select Case .Fields(24).Value >= (1.1 * CSng(.Fields(25).Value))
                        Case False
                            .Fields(2).Value = "Production"
                            .Fields(13).Value = .Fields(12).Value * 650 * 0.6 / .Fields(3).Value
                            Select Case .Fields(23).Value
                            Case 0
                                .Fields(22).Value = 0
                            Case 0.1 To 5
                                .Fields(22).Value = 12
                            Case 5 To 9.9
                                .Fields(22).Value = 8
                            Case 10 To 14.9
                                .Fields(22).Value = 6
                            Case 15 To 19.9
                                .Fields(22).Value = 4
                            Case 20 To 25
                                .Fields(22).Value = 3
                            Case Is > 25
                                .Fields(22).Value = 2
                            End Select
                            
                            .Fields(18).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Distribution Volume at that time.
                            .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                            If .Fields(11).Value <= 2 Then
                                .Fields(11).Value = 0  'Assumes if nitrate is less than 2 it is likely in error.
                            End If
                        Case Else
                            .Fields(2).Value = "Blended Flushing"
                            .Fields(15).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Filter-to-Waste Volume at that time.
                            .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                            .Fields(13).Value = 0
                            .Fields(22).Value = 0
                        End Select
                    Else
                        .Fields(2).Value = "Production"
                        .Fields(13).Value = .Fields(12).Value * 650 * 0.6 / .Fields(3).Value
                        Select Case .Fields(23).Value
                        Case 0
                            .Fields(22).Value = 0
                        Case 0.1 To 5
                            .Fields(22).Value = 12
                        Case 5 To 9.9
                            .Fields(22).Value = 8
                        Case 10 To 14.9
                            .Fields(22).Value = 6
                        Case 15 To 19.9
                            .Fields(22).Value = 4
                        Case 20 To 25
                            .Fields(22).Value = 3
                        Case Is > 25
                            .Fields(22).Value = 2
                        End Select
                        
                        .Fields(18).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Distribution Volume at that time.
                        .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                        If .Fields(11).Value <= 2 Then
                            .Fields(11).Value = 0  'Assumes if nitrate is less than 2 it is likely in error.
                        End If
                    End If
                    
                Case Else
                        .Fields(2).Value = "Filter-to-Waste"
                        .Fields(15).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Filter-to-Waste Volume at that time.
                        .Fields(13).Value = 0
                        .Fields(22).Value = 0
                End Select
                
                .Fields(21).Value = .Fields(17).Value * 5 'Backwash Storage volume from well at that time.
                .Fields(20).Value = .Fields(4).Value * 5 'Discharge volume from well at that time.
                .Fields(16).Value = .Fields(3).Value * 5 'Treated volume at that time.
                blnWrite = True
                
            Case (.Fields(3).Value > 20 And .Fields(5).Value = 0 And .Fields(4).Value = 0)
                
                'System just came online.
                .Fields(1).Value = "Online"
                blnOffline = False
                                    
                intRowStart = lngIndex1
                
                If blnBRBW = True And intBROnlineCounter > 623 Then  '52 hours in 5 minute intervals
                    intBROnlineCounter = 0
                    blnBRBW = False
                    dteBRStart = .Fields(0).Value
                ElseIf blnBRBW = True Then
                    intBROnlineCounter = intBROnlineCounter + 1
                    blnBRBW = False
                    dteBRStart = .Fields(0).Value
                Else
                    intBROnlineCounter = intBROnlineCounter + 1
                End If
                
                If blnBFBW = True And intBFOnlineCounter > 864 Then  '72 hours in 5 minute intervals
                    intBFOnlineCounter = 0
                    blnBFBW = False
                    dteBFStart = .Fields(0).Value
                ElseIf blnBFBW = True Then
                    intBFOnlineCounter = intBFOnlineCounter + 1
                    blnBFBW = False
                    dteBFStart = .Fields(0).Value
                Else
                    intBFOnlineCounter = intBFOnlineCounter + 1
                End If
                
                'Next, I need to determine the mode of operation.
                Select Case .Fields(4).Value >= (1.1 * (.Fields(3).Value + .Fields(17).Value))
                Case True
                
                    If Len(.Fields(25).Value) > 0 Then
                        Select Case .Fields(24).Value >= (1.1 * CSng(.Fields(25).Value))
                        Case False
                            .Fields(2).Value = "Production"
                            .Fields(13).Value = .Fields(12).Value * 650 * 0.6 / .Fields(3).Value
                            Select Case .Fields(23).Value
                            Case 0
                                .Fields(22).Value = 0
                            Case 0.1 To 5
                                .Fields(22).Value = 12
                            Case 5 To 9.9
                                .Fields(22).Value = 8
                            Case 10 To 14.9
                                .Fields(22).Value = 6
                            Case 15 To 19.9
                                .Fields(22).Value = 4
                            Case 20 To 25
                                .Fields(22).Value = 3
                            Case Is > 25
                                .Fields(22).Value = 2
                            End Select
                            
                            .Fields(18).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Distribution Volume at that time.
                            .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                            If .Fields(11).Value <= 2 Then
                                .Fields(11).Value = 0  'Assumes if nitrate is less than 2 it is likely in error.
                            End If
                        Case Else
                            .Fields(2).Value = "Blended Flushing"
                            .Fields(15).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Filter-to-Waste Volume at that time.
                            .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                            .Fields(13).Value = 0
                            .Fields(22).Value = 0
                        End Select
                    Else
                        .Fields(2).Value = "Production"
                        .Fields(13).Value = .Fields(12).Value * 650 * 0.6 / .Fields(3).Value
                        Select Case .Fields(23).Value
                        Case 0
                            .Fields(22).Value = 0
                        Case 0.1 To 5
                            .Fields(22).Value = 12
                        Case 5 To 9.9
                            .Fields(22).Value = 8
                        Case 10 To 14.9
                            .Fields(22).Value = 6
                        Case 15 To 19.9
                            .Fields(22).Value = 4
                        Case 20 To 25
                            .Fields(22).Value = 3
                        Case Is > 25
                            .Fields(22).Value = 2
                        End Select
                        
                        .Fields(18).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Distribution Volume at that time.
                        .Fields(19).Value = ((.Fields(4).Value - .Fields(3).Value - .Fields(17).Value) * 5) 'Bypassed volume.
                        If .Fields(11).Value <= 2 Then
                            .Fields(11).Value = 0  'Assumes if nitrate is less than 2 it is likely in error.
                        End If
                    End If
                    
                Case Else
                        .Fields(2).Value = "Filter-to-Waste"
                        .Fields(13).Value = 0
                        .Fields(22).Value = 0
                        .Fields(15).Value = ((.Fields(3).Value - .Fields(17).Value) * 5) 'Filter-to-Waste Volume at that time.
                End Select
                
                .Fields(21).Value = .Fields(17).Value * 5 'Backwash Storage volume from well at that time.
                .Fields(20).Value = .Fields(4).Value * 5 'Discharge volume from well at that time.
                .Fields(16).Value = .Fields(3).Value * 5 'Treated volume at that time.
                blnWrite = True
                
            Case (.Fields(3).Value <= 0 And .Fields(5).Value = 0)
            
                'System just went offline but has not yet backwashed.
                .Fields(1).Value = "Offline"
                blnOffline = True
                
                blnWrite = True
                
            Case (.Fields(3).Value <= 0 And .Fields(5).Value > 0) And (blnBRBW = False And blnBFBW = False)
                blnOffline = True
                
                'strWhat = funWhatBackwashed(lngIndex1, intBROnlineCounter, intBFOnlineCounter)
                strWhat = funWhatBackwashed2(.Fields(0).Value, intBROnlineCounter, intBFOnlineCounter, strTableName, .Fields(6).Value, .Fields(7).Value)
    
                Select Case strWhat
                Case "Bioreactor"
                    'intBROnlineCounter = 0
                    .Fields(1).Value = "BW BR"
                    dteBREnd = .Fields(0).Value
                    blnBRBW = True
                    intWhatBW = 1 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                Case "Biofilter"
                    'intBFOnlineCounter = 0
                    .Fields(1).Value = "BW BF"
                    dteBFStop = .Fields(0).Value
                    blnBFBW = True
                    intWhatBW = 2 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                Case "Both"
                    'intBROnlineCounter = 0
                    'intBFOnlineCounter = 0
                    .Fields(1).Value = "BW Both"
                    dteBREnd = .Fields(0).Value
                    dteBFStop = .Fields(0).Value
                    blnBRBW = True
                    blnBFBW = True
                    intWhatBW = 3 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                End Select
                
                .Fields(14).Value = (.Fields(5).Value - .Fields(17).Value) * 5 'Backwash volume at that time
                If .Fields(14).Value > 10000 Then
                    .Fields(14).Value = 0
                End If
                    
                blnWrite = False
            
            Case (.Fields(5).Value > 0) And (blnBRBW = False And blnBFBW = False)
    
                blnOffline = True
                
                'strWhat = funWhatBackwashed(lngIndex1, intBROnlineCounter, intBFOnlineCounter)
                strWhat = funWhatBackwashed2(.Fields(0).Value, intBROnlineCounter, intBFOnlineCounter, strTableName, .Fields(6).Value, .Fields(7).Value)
                
                Select Case strWhat
                Case "Bioreactor"
                    'intBROnlineCounter = 0
                    .Fields(1).Value = "BW BR"
                    dteBREnd = .Fields(0).Value
                    blnBRBW = True
                    intWhatBW = 1 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                Case "Biofilter"
                    'intBFOnlineCounter = 0
                    .Fields(1).Value = "BW BF"
                    dteBFStop = .Fields(0).Value
                    blnBFBW = True
                    intWhatBW = 2 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                Case "Both"
                    'intBROnlineCounter = 0
                    'intBFOnlineCounter = 0
                    .Fields(1).Value = "BW Both"
                    dteBREnd = .Fields(0).Value
                    dteBFStop = .Fields(0).Value
                    blnBRBW = True
                    blnBFBW = True
                    intWhatBW = 3 'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                End Select
                
                .Fields(14).Value = (.Fields(5).Value - .Fields(17).Value) * 5 'Backwash volume at that time
                If .Fields(14).Value > 10000 Then
                    .Fields(14).Value = 0
                End If
                blnWrite = False
                
            Case Else
            
                'Something else happened
                .Fields(1).Value = "Unknown"
                blnWrite = False
                blnOffline = True
            End Select
            
            
            If blnOffline = True Then
                'This empties out the flow rates when the system is offline.  Need to empty out everything else as well, but do that separately.
                .Fields(3).Value = Null
                .Fields(4).Value = Null
                .Fields(13).Value = Null
                .Fields(22).Value = Null
            Else  'Its online so please write everything else.
                
                .Fields(6).Value = intBROnlineCounter * 5 / 60
                .Fields(7).Value = intBFOnlineCounter * 5 / 60
                .Fields(8).Value = intBRRunCounter
                .Fields(9).Value = intBFRunCounter
                If RS.Fields(3).Value <> 0 Then
                    .Fields(10).Value = sngUnitVol / RS.Fields(3).Value
                Else
                    .Fields(10).Value = Null
                End If
                
                If intWhatBW <> 0 Then  'variable used to keep track of what backwashed.  '1 = BR, 2 = BF, 3 = Both
                    If intWhatBW = 1 Then
                        intBRRunCounter = intBRRunCounter + 1
                    ElseIf intWhatBW = 2 Then
                        intBFRunCounter = intBFRunCounter + 1
                    ElseIf intWhatBW = 3 Then
                        intBRRunCounter = intBRRunCounter + 1
                        intBFRunCounter = intBFRunCounter + 1
                    End If
                    intWhatBW = 0
                End If
            End If
            .Update
        End With
               
        'Then after 1000 rows, update status bar progress
        If (lngIndex1 Mod 1000) = 0 Then
            'Update the progressbar
            modConfig.updStatusBarProgress (lngIndex1)  'turn this back on after testing.
            wkspDAO.CommitTrans dbForceOSFlush
            wkspDAO.BeginTrans
        End If
        RS.MoveNext
    Next lngIndex1

    Erase strarStatusData()
    wkspDAO.CommitTrans dbForceOSFlush
    wkspDAO.Close
    'RS.Close
    'dbProformance.Close
    Set wkspDAO = Nothing
    Set RS = Nothing
    Set dbProformance = Nothing
    
    
    Call modAnalysis.DelanoFullScaleZeroOffline(strTableName & "_U" & intUnitNumStatus, intUnitNumStatus)
    
ErrorHandler:
    'debug.print Error(Err)
    'MsgBox "Error occurred during raw unit locator writing. Error = " & Error(Err), vbOKOnly
    
    If Error(Err) = "Invalid use of Null" Then
        Resume Next
    ElseIf Error(Err) = "Type Mismatch" Then
        Resume Next
    Else
        'debug.print Error(Err)
        
        ErrorReport.rcsErr = Err
        ErrorReport.rcsErrDesc = Error(Err)
        ErrorReport.rcsProcedure = "DelanoFullscaleStatus2"  'Edit per procedure
            
        If ErrorReport.rcsErr = 0 Then
            ErrorReport.rcsProcTrail = ErrorReport.rcsProcTrail & ErrorReport.rcsProcedure & ", Successful (" & ReturnVirtualMemory / 1024 / 1024 & " MB); "
            Exit Sub
        Else
            ErrorReport.rcsProcTrail = ErrorReport.rcsProcTrail & ErrorReport.rcsProcedure & ", Failed (" & ReturnVirtualMemory / 1024 / 1024 & " MB); "
            'ErrorReport.rcsErrLine = Erl
        End If
            
        ReDim ErrorReport.rcsVariables(1 To 21)  'Edit this end following block per procedure
            ErrorReport.rcsVariables(1) = "(Passed) strTableName = " & strTableName
            ErrorReport.rcsVariables(2) = "(Passed) intUnitNumStatus = " & intUnitNumStatus
            ErrorReport.rcsVariables(3) = "(Dim) intRowCounter = " & intRowCounter
            ErrorReport.rcsVariables(4) = "(Dim) intRowStart = " & intRowStart
            ErrorReport.rcsVariables(5) = "(Dim) dteBRStart = " & dteBRStart
            ErrorReport.rcsVariables(6) = "(Dim) dteBREnd = " & dteBREnd
            ErrorReport.rcsVariables(7) = "(Dim) dteBFStart = " & dteBFStart
            ErrorReport.rcsVariables(8) = "(Dim) dteBFStop = " & dteBFStop
            ErrorReport.rcsVariables(9) = "(Dim) intBRRunCounter = " & intBRRunCounter
            ErrorReport.rcsVariables(10) = "(Dim) intBFRunCounter = " & intBFRunCounter
            ErrorReport.rcsVariables(11) = "(Dim) intBROnlineCounter = " & intBROnlineCounter
            ErrorReport.rcsVariables(12) = "(Dim) intBFOnlineCounter = " & intBFOnlineCounter
            ErrorReport.rcsVariables(13) = "(Dim) sngRunTime = " & sngRunTime
            ErrorReport.rcsVariables(14) = "(Dim) blnBRBW = " & blnBRBW
            ErrorReport.rcsVariables(15) = "(Dim) blnBFBW = " & blnBFBW
            ErrorReport.rcsVariables(16) = "(Dim) blnOffline = " & blnOffline
            ErrorReport.rcsVariables(17) = "(Dim) blnWrite = " & blnWrite
            ErrorReport.rcsVariables(18) = "(Dim) strWhat = " & strWhat
            ErrorReport.rcsVariables(19) = "(Dim) sngUnitVol = " & sngUnitVol
            ErrorReport.rcsVariables(20) = "(Global) strSQL = " & strSQL
            ErrorReport.rcsVariables(21) = "(Global) strStatusData = " & strStatusData

                    
        Call modConfig.ErrorReportOutput
    
    End If
    
End Sub

[angeleumbra]

To compile my code, you'll need my Global Variables (which are cleared regularly and should not be causing any problems unless the garbage collector is not functioning appropriately) and my funWhatBackwashed2. See below.

Option Compare Database
Option Explicit

Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Public Declare PtrSafe Function GlobalMemoryStatus Lib "Kernel32.dll" (lpBuffer As MemoryStatus) As Long
Public Declare Function getDesktopWindow Lib "user32" () As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

'Form Input Variables:
Public strFacName As String
Public strDesc As String
Public strProcess As String
Public strTemp As String
Public strarTemp() As String
Public intUnitNum As Integer
Public intRawWaters As Integer
Public intPreProcessNum As Integer
Public blnFlowDataAll As Boolean
Public strHLDefUnit As String
Public strFlowDefUnit As String
Public strPresDefUnit As String
Public intHeaderRows As Integer
Public strDirectory As String
Public strarDirectory() As String  'Used for multiple imports

'Strings and String Arrays
Public strSQL As String
Public strarColNames() As String  'Used to populate the form
Public strSQLCol As String  'Used to hold the name we care about and the column of information.
Public strSQLColSys As String  'Used to hold the name we care about and the column of information.
Public strRawNames As String
Public strarSystem() As String
Public strStatusData As String  'Used to hold the start and stop times of the bioreactor/biofilter for analysis: comma deliminated then semicolon
Public strarStatusData() As Variant  'Used to hold the actual data for use.
Public strarTemp1() As String
Public strarTemp2() As String
Public strBDFieldNames As String
Public strBDValidOffline As String
Public strLSFieldNames As String
Public strarImport() As String  'Probably going away
Public strarExport() As String  'For exporting data to Excel.
'Public strarGraph() As String  'For exporting graphs to Excel.
Public strTableName2 As String
Public strTemp1 As String

'Numbers: Integers, Singles, Doubles...
Public dteStartProcess As Date
Public intCounter As Integer
Public intColumnCount As Integer
Public intRecordCount As Integer
Public lngRowCount As Long
Public intProcessKey As Integer
Public lngIndex1 As Long
Public lngIndex2 As Long
Public lngIndex3 As Long
Public intFieldCount As Integer

'Booleans: Yes/No and Flags...
Public blnNoSystem As Boolean  'Used to indicate there is no system specific information.
Public blnWrittenUnitRawLocator As Boolean
Public blnWrittenSystemRawLocator As Boolean

'Database Tools...
Public dbProformance As DAO.Database
Public RS As DAO.Recordset
Public QDF As DAO.QueryDef


'InterApplication Utilities
Public appExcel As Excel.Application
Public wkbImport As Excel.Workbook
Public shtImport As Excel.Worksheet
Public shtPlot As Excel.Chart
Public serPlot As Excel.Series
Public appAccessImport As Access.Application
Public dbImport As DAO.Database

' Report Variables For Table Selection

Public strSelectReportType As String
Public strSelectFacility As String


'Reference Class for Modules
Public Type udtFilterRun
    rcsBRRunCounter As Integer
    rcsBFRunCounter As Integer
    rcsStartDate As Date
    rcsEndDate As Date
    rcsRuntime As Single
    rcsAvgQIn As Single
    rcsAvgQEff As Single
    rcsUFRV As Single
    rcsBWVolume As Single
    rcsRecovery As Single
    rcsCBHL_BR As Single
    rcsTHL_BR As Single
    rcsCBHL_BF As Single
    rcsCBTurb_BF As Single
    rcsTHL_BF As Single
    rcsTTurb_BF As Single
End Type

Public Type udtErrorRep
    rcsErr As Integer
    rcsErrDesc As String
    rcsErrLine As Long
    rcsProcedure As String
    rcsVariables() As String
    rcsProcTrail As String  'Comma deliminated for "Procedure, Status", Semi-Colon deliminated for each procedure previously
End Type

Public Type udtSeriesData
    intSeriesNum As Integer
    txtSeriesName As String
    intYAxisColumn As Integer
    txtYAxisTitle As String  'This is legacy for delano. Changed to udtGraphData.txtPriYTitle and similar.
    txtSeriesType As String
    sngOpConstant As Single
    blnSecondary As Boolean  'True = secondary...
    txtLabelText As String
    sngYCap As Single
    txtManXRange As String
    blnVertConstFake As Boolean
    
    'Series Format Variables
    txtMarkerStyle As String
    intMarkerForeColor As Integer
    intMarkerBackColor As Integer
    sngMarkerSize As Single
    strLineStyle As String
    intLineColor As Integer
    sngLineWeight As Single
    blnHasLabels As Boolean
    strLabelPosition As String
End Type
    
Public Type udtGraphData
    blnHeader As Boolean 'Printed Header Text
    txtLeftHeader As String
    txtCenterHeader As String
    txtRightHeader As String
    
    blnFooter As Boolean 'Printed Footer Text
    txtLeftFooter As String
    txtCenterFooter As String
    txtRightFooter As String
    
    txtPlotTitle As String
    txtTabName As String
    blnLegend As Boolean
    
    intXAxisColumn As Integer
    txtXAxisTitle As String
    txtXAxisDataNF As String
    txtXStart As String
    txtXEnd As String
    txtXNF As String
    
    txtPriYTitle As String
    txtSecYTitle As String
    txtPriYNF As String
    sngPriYCap As Single
    txtSecYNF As String
    sngSecYCap As Single
    strarSeries() As udtSeriesData
    txtFileName As String
    
    'Plot format variables
    txtPlotFont As String
    sngFontSizeTitles As Single
    sngFontSizeTickLabels As Single
    intPlotBorderColor As Integer
    strPlotBorderLineStyle As String
    sngPlotBorderWidth As Single
    blnVertMajorGrid As Boolean
    blnHoriMajorGrid As Boolean
End Type

Public Type MemoryStatus
    lngLength As Long
    lngMemoryLoad As Long
    lngTotalPhy As Long
    lngAvailPhy As Long
    lngTotalPageFile As Long
    lngAvailPageFile As Long
    lngTotalVirtual As Long
    lngAvailVirtual As Long
End Type
    
Public RunCycle As udtFilterRun
Public ErrorReport As udtErrorRep
Public strarGraph() As udtGraphData

Private Function funWhatBackwashed2(dteSearch As Date, intBROnlineCounter As Long, intBFOnlineCounter As Long, strTableName As String, _
    sngBRRuntime As Single, sngBFRuntime As Single) As String
    'Created 3/14/2017 to alleviate some of the headache Giridhar has been having with System Resource Exceeded errors.
    Dim strWhat As String
    Dim sngBRMax10minus As Single
    Dim sngBRMin10plus As Single
    Dim sngBRMax10plus As Single
    Dim sngBFMax10minus As Single
    Dim sngBFMin10plus As Single
    Dim sngBFMax10plus As Single
    Dim varValues As Variant
    Dim RSJustBecause As DAO.Recordset
    
    On Error GoTo ErrorHandler
    
    Set dbProformance = CurrentDb
    
    strSQL = "SELECT col_U1_RT_BR " & "FROM " & strTableName & " WHERE dteTimeStamp " _
        & "BETWEEN #" & dteSearch - (5 / 60 / 24) * 9 & "# and #" & dteSearch & "#;"
    Set RSJustBecause = dbProformance.OpenRecordset(strSQL)
    RSJustBecause.MoveLast
    RSJustBecause.MoveFirst
    varValues = RSJustBecause.GetRows(RSJustBecause.RecordCount)
    
    sngBRMax10minus = CSng(Excel.WorksheetFunction.Max(varValues))
    RSJustBecause.Close
    Set RSJustBecause = Nothing
    Erase varValues

    strSQL = "SELECT col_U1_RT_BR " & "FROM " & strTableName & " WHERE dteTimeStamp " _
        & "BETWEEN #" & dteSearch & "# and #" & dteSearch + (5 / 60 / 24) * 9 & "#;"
    Set RSJustBecause = dbProformance.OpenRecordset(strSQL)
    RSJustBecause.MoveLast
    RSJustBecause.MoveFirst
    varValues = RSJustBecause.GetRows(RSJustBecause.RecordCount)
    
    sngBRMin10plus = CSng(Excel.WorksheetFunction.Min(varValues))
    sngBRMax10plus = CSng(Excel.WorksheetFunction.Max(varValues))
    RSJustBecause.Close
    Set RSJustBecause = Nothing
    Erase varValues
    
    strSQL = "SELECT col_U1_RT_BF " & "FROM " & strTableName & " WHERE dteTimeStamp " _
        & "BETWEEN #" & dteSearch - (5 / 60 / 24) * 9 & "# and #" & dteSearch & "#;"
    Set RSJustBecause = dbProformance.OpenRecordset(strSQL)
    RSJustBecause.MoveLast
    RSJustBecause.MoveFirst
    varValues = RSJustBecause.GetRows(RSJustBecause.RecordCount)

    sngBFMax10minus = CSng(Excel.WorksheetFunction.Max(varValues))
    RSJustBecause.Close
    Set RSJustBecause = Nothing
    Erase varValues

    strSQL = "SELECT col_U1_RT_BF " & "FROM " & strTableName & " WHERE dteTimeStamp " _
        & "BETWEEN #" & dteSearch & "# and #" & dteSearch + (5 / 60 / 24) * 9 & "#;"
    Set RSJustBecause = dbProformance.OpenRecordset(strSQL)
    RSJustBecause.MoveLast
    RSJustBecause.MoveFirst
    varValues = RSJustBecause.GetRows(RSJustBecause.RecordCount)

    sngBFMin10plus = CSng(Excel.WorksheetFunction.Min(varValues))
    sngBFMax10plus = CSng(Excel.WorksheetFunction.Max(varValues))
    RSJustBecause.Close
    Set RSJustBecause = Nothing
    Erase varValues
    
    Select Case True
    Case sngBRRuntime = sngBRMax10minus And sngBRRuntime > sngBRMin10plus 'Compare BR Runtime
        strWhat = "Bioreactor"
    Case intBROnlineCounter > 623  '52 hours in 5 minute intervals
        strWhat = "Bioreactor"
    Case Else
        strWhat = ""
    End Select
    
    Select Case True
    Case sngBFRuntime = sngBFMax10minus And sngBFRuntime > sngBFMin10plus 'Compare BF Runtime
        If strWhat = "" Then
            strWhat = "Biofilter"
        Else
            strWhat = "Both"
        End If
    Case intBFOnlineCounter > 864  '72 hours in 5 minute intervals
        If strWhat = "" Then
            strWhat = "Biofilter"
        Else
            strWhat = "Both"
        End If
    Case Else
        strWhat = strWhat  'Bioreactor triggered and Biofilter did not.
    End Select
    
    'If strarStatusData(6, lngIndex1) < sngBRMax10plus And strarStatusData(7, lngIndex1) < sngBFMax10plus Then
        'strWhat = "Both"
    'End If
    
    If strWhat = "" Then
        lngIndex1 = lngIndex1
    End If
    
    'Debug.Print strWhat
    funWhatBackwashed2 = strWhat
    
    
ErrorHandler:
    'debug.print Error(Err)

    ErrorReport.rcsErr = Err
    ErrorReport.rcsErrDesc = Error(Err)
    ErrorReport.rcsProcedure = "funWhatBackwashed2 (Function)"  'Edit per procedure

    If ErrorReport.rcsErr = 0 Then
        ErrorReport.rcsProcTrail = ErrorReport.rcsProcTrail & ErrorReport.rcsProcedure & ", Successful (" & ReturnVirtualMemory / 1024 / 1024 & " MB); "
        Exit Function
    Else
        ErrorReport.rcsProcTrail = ErrorReport.rcsProcTrail & ErrorReport.rcsProcedure & ", Failed (" & ReturnVirtualMemory / 1024 / 1024 & " MB); "
        'ErrorReport.rcsErrLine = Erl
    End If

    ReDim ErrorReport.rcsVariables(1 To 11)  'Edit this end following block per procedure
        ErrorReport.rcsVariables(1) = "(Passed) dteSearch = " & dteSearch
        ErrorReport.rcsVariables(2) = "(Passed) intBROnlineCounter = " & intBROnlineCounter
        ErrorReport.rcsVariables(3) = "(Passed) intBFOnlineCounter = " & intBFOnlineCounter
        ErrorReport.rcsVariables(4) = "(Dim) strWhat = " & strWhat
        ErrorReport.rcsVariables(5) = "(Dim) sngBRMax10minus = " & sngBRMax10minus
        ErrorReport.rcsVariables(6) = "(Dim) sngBRMin10plus = " & sngBRMin10plus
        ErrorReport.rcsVariables(7) = "(Dim) sngBRMax10plus = " & sngBRMax10plus
        ErrorReport.rcsVariables(8) = "(Dim) sngBFMax10minus = " & sngBFMax10minus
        ErrorReport.rcsVariables(9) = "(Dim) sngBFMin10plus = " & sngBFMin10plus
        ErrorReport.rcsVariables(10) = "(Dim) sngBFMax10plus = " & sngBFMax10plus
        ErrorReport.rcsVariables(11) = "(Globabl) strSQL = " & strSQL

    Call modConfig.ErrorReportOutput

    'Give me the error and let me try to fix it.  How do I tell it to do that?
    'On Error GoTo 0
    'Resume


End Function

[aytee111]

The CommitTrans is not the cause of the increase in size. Databases naturally increase due to deleting and adding, which was why the Compact was provided. This is a normal result of your requirements and design and Access itself, no getting around it. Compacts do sometimes crash, especially if dependent on networks, so it is advised that a copy be made to a local drive, compacted, and copied back. Or at least make a copy prior to compacting.

[John_G]

The data for the 1000 records that are updated but before transaction has been committed has to be put somewhere (or the existing data has to be copied somewhere) before the transaction is committed, just in case you need to do a rollback. 400b per record is not excessive, so 1000 record would give you the 400kB. Now, if access doesn't reuse that temporary space, you would get the 400kB increase each time. 79,000 records would still only result in 35MB or so, so why you are getting 335MB, I really can't say.

MS Access will bloat the database for any number of reasons - temporary tables, complex queries (they can be really bad), frequent addition/deletion of records etc.

You could turn on the "Compact on Close" option (File - Options - Current Database) , which would save you having to do it manually.

[angeleumbra]

Thanks for your responses folks. I posted the same question on another forum and got a response early this morning. So for everyone else, here's the skinny.

The CommitTrans was in fact causing the increase in size in excess of adding new data. Every time the "evaluation" button was clicked, the data source was deleted and re-added (on purpose) and so in theory if the cause of the file size increase was just due to adding new data then each successive click of the button would delete everything added the first time and re-add that information and thus should have resulted in the same file size between successive runs. However, the file continued to grow even though the quantity of data did not.

The solution was dead simple. For those novices out there, like myself, whenever you open a recordset there are Access client settings that tell the database if those records need to be locked. In my case, my default record locking method was "No locks," but I had "Open databases by using record-level locking" checked. By un-checking this box, my database bloats at a breath-taking 40kb per thousand records (instead of 4000kb/1000 records). I can easily handle this with a manual compact and repair on a weekly basis which will allow me to make a copy of the database and not worry about data corruption. Changing this setting also allowed some of my users who regularly received "system resource exceeded" errors to use the database without any issues, so this was a bonus success.

Thanks for your help guys! Just to reiterate, the solution was to go to "File -> Options -> Client Settings -> Advanced" and uncheck the "Open databases by using record-level locking" with "no locks" as the default record locking method.

[aytee111]

Thank you for posting the solution, very informative.

[NTC]

While this may have been the solution for this specific application/situation - just to be clear to new designers - one generally should not re-option like this. The default settings should typically not be changed, they are: 'No Locks' and checked 'Open databases by using record-level locking'

[ry94080]

Thanks for your responses folks. I posted the same question on another forum and got a response early this morning. So for everyone else, here's the skinny.

The CommitTrans was in fact causing the increase in size in excess of adding new data. Every time the "evaluation" button was clicked, the data source was deleted and re-added (on purpose) and so in theory if the cause of the file size increase was just due to adding new data then each successive click of the button would delete everything added the first time and re-add that information and thus should have resulted in the same file size between successive runs. However, the file continued to grow even though the quantity of data did not.

The solution was dead simple. For those novices out there, like myself, whenever you open a recordset there are Access client settings that tell the database if those records need to be locked. In my case, my default record locking method was "No locks," but I had "Open databases by using record-level locking" checked. By un-checking this box, my database bloats at a breath-taking 40kb per thousand records (instead of 4000kb/1000 records). I can easily handle this with a manual compact and repair on a weekly basis which will allow me to make a copy of the database and not worry about data corruption. Changing this setting also allowed some of my users who regularly received "system resource exceeded" errors to use the database without any issues, so this was a bonus success.

Thanks for your help guys! Just to reiterate, the solution was to go to "File -> Options -> Client Settings -> Advanced" and uncheck the "Open databases by using record-level locking" with "no locks" as the default record locking method.

Hello - thank you for posting this solution as it also helped me with my bloating issues. I'm wondering if you would happen to know if this "no locks" configuration was configurable via VBA?