FE Database Still Experiencing Bloat (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 17:46
Joined
Oct 21, 2021
Messages
291
I know Cathy, but we've been running queries and reports long before the tempvars :) Again, I have not used them in such an extensive way as you are in here, but it looks like a good place to start. For example in your cmdReportPDF_Click procedure why can't the report's (rptCPReport) recordsource reference the form controls directly instead of removing all tempvars and recreating them.

But I thing the biggest impact is in the Illinois_Split procedure where you are adding tempvars for each record while looping through rsJobs recordset (and I don't see any RemoveAll).

Cheers,
Ok Vlad, i will look at it. I tried using controls on forms (which are on a navigation form) and I couldn't test the form by itself plus the reports and queries are used in multiple forms. I recently learned about TempVars ((probably here) and gave them a slot.

Your suggestion to do a RemoveAll within the loop makes sense, so I will add that, thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:46
Joined
Feb 19, 2013
Messages
14,705
Corruption could be possible,
I'm sure this will have been suggested before, but if corruption is suspected then suggest create a new db and import your existing table and all queries/forms/reports and modules, then relink to the BE for the remaining tables and finally compile your code and apply any other db properties such as specifying which form to open
 

AngelSpeaks

Active member
Local time
Today, 17:46
Joined
Oct 21, 2021
Messages
291
I'm sure this will have been suggested before, but if corruption is suspected then suggest create a new db and import your existing table and all queries/forms/reports and modules, then relink to the BE for the remaining tables and finally compile your code and apply any other db properties such as specifying which form to open
I did do this before. I am using an Export Specifcation for the query that creates the csv. Do you know how I can copy that over? Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 28, 2001
Messages
23,166
To further amplify my previous comments:

A back-end bloats when you do DELETE or UPDATE queries because you cannot garbage collect the deleted records or the "before" copies of the records on an update. Since BEGIN/COMMIT could have a ROLLBACK, and since db.EXECUTE SQL has a potential single-query rollback, the updated records temporarily co-exist with the original records. One or the other of those sets of records will be deleted, which will contribute to the bloat.

A front-end bloats because that is where lists are built - the location of Workspace(0).

If you have a "side-end" file of temporary space, it bloats for DELETE or UPDATE like the back-end does - but the FE still bloats because of list building, which cannot occur in the side-end file.

The question often comes up: What lists?

If you look up "Order of SQL Operations" you will see the way that an SQL engine has to work in a specific way to do its job.


The record list gets built initially when evaluating the FROM and JOIN clauses, then gets trimmed by honoring WHERE and HAVING clauses later. GROUP and ORDER BY operations don't change list size but might change its order. SELECT doesn't actually change the list, it just specifies which fields are to be presented based on the list. Note that the presence of a TOP n predicate doesn't change the list - it just limits the presentation. This should tell you where to expect bloat - and when.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 28, 2001
Messages
23,166
A single-record table that is not updated is stable in size. Tables can grow due to INSERT INTO queries but that isn't bloat, it is data growth. Decidedly not wasted space.

Exception, of sorts: an INSERT INTO ... FROM (SELECT... FROM can cause bloat in the table targeted by the FROM since there is an implied list in the FROM clause.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:46
Joined
Feb 19, 2013
Messages
14,705
I am using an Export Specifcation for the query that creates the csv. Do you know how I can copy that over?
when importing, click on the options button, it is one of the things you can include
 

AngelSpeaks

Active member
Local time
Today, 17:46
Joined
Oct 21, 2021
Messages
291
Background: have to create csv files to upload to State Portal. Excel template has 96 columns, many with duplicate names. CSV files created by Access were not being accepted unless they were saved as csv by Excel.

Here's an update. Recreated database by copying objects into blank one. Made some minor coding adjustments.
New Version Size - 18,048 kb.
Made a backup and then .accde. Size 3,616 kb

Monthly Processing

PW Reporting by Check Date (very popular and critical report) -
no change in size.

Weekly Processing - critical. Creates upload to State Portal.

Ran CP Preparation - dCount on six queries, if greater than 0, ran reports -
no change in size

CP Report - full report - query has 96 columns . Joins tables tblPWBenefits, tblJobs, tblEmployees, tblPublicBody, tblEmployer
Size changed to 4,412 kb

CP Export - same query, has 96 columns
loop for each job
creates csv file
run code to change column headings -
Needed because goofy state used duplicate column headers
Excel automation to read in csv file and save it -
Needed because goofy state can only import .csv files saved by Excel
Creates one page report to pdf for that job
.csv and pdf files get uploaded to State Portal
Size changed to 15,736 kb - increase of 11,324kb!


On Demand Reporting
CP Report - by job (same report as weekly processing)
Size changed to 16,332 kb

CP Report - by job to PDF and then Outlook Automation to Email Report
Size changed to 16,928 kb

Semi Monthly Processing - Critical

Delete Check Run (to rerun a Check Run only) - delete query to delete records for a check run
Size changed to 16,936 kb

Processed Check Run -
Import Excel spreadsheet of hours worked to tblHoursWorked
Append query to add transactions to tblPWBenefits
Update query, join to tblPWRates to extract rates and calculate wages, benefits, etc
Delete query to delete all records to tblHoursWorked
Size changed to 17,016 kb

CP Export code
Code:
Private Sub cmdExport_Click()
    'Check to see if State has a portal to upload to
    Select Case gblnPortal
        Case False
    Exit Sub
        Case True
    End Select
    
    msgApp = "Certified Payroll"
    
    'Check to make sure state was selected
    If IsNull(Me.cboState.Value) Then
        msgText = "Please select a state from the list"
        Response = MsgBox(msgText, vbOKOnly, msgApp)
        Exit Sub
    End If
        
    'Dim dbs As dao.Database
    'Dim qdf As dao.QueryDef
   ' Dim rst As dao.Recordset
    If DCount("*", "qryMissingEmployees") > 0 Then
        msgText = "Employees are Missing - Procedure Stopped"
        Response = MsgBox(msgText, vbOKOnly, msgApp)
        Exit Sub
    End If
    If DCount("*", "qryMissingJobs") > 0 Then
        msgText = "Jobs are Missing - Procedure Stopped"
        Response = MsgBox(msgText, vbOKOnly, msgApp)
        Exit Sub
    End If
    If DCount("*", "qryMissingPublicBody") > 0 Then
        msgText = "Public Body records are Missing - Procedure Stopped"
        Response = MsgBox(msgText, vbOKOnly, msgApp)
        Exit Sub
    End If
    dteStart = Me.StartDate
    dteEnd = Me.EndDate
    
    If IsNull(TempVars("StartDate") = "") Then
        TempVars.Add "StartDate", dteStart
    Else
        TempVars!StartDate = dteStart
    End If
    
    If IsNull(TempVars("EndDate") = "") Then
        TempVars.Add "EndDate", dteEnd
    Else
        TempVars!EndDate = dteEnd
    End If
    
    If IsNull(TempVars("State") = "") Then
        TempVars.Add "State", gstrState
    Else
        TempVars!State = gstrState
    End If
            
    Dim strCriteria As String                  'Date criteria to extract unique jobs
    
    Select Case gstrState
        Case "IL"
            Illinois_Split
    End Select
End Sub

Private Sub Illinois_Split()
    Dim sDate As String
    Dim lRecCount As Integer
    
    'For renaming columns
    Dim sFile As String
    Dim sFieldNamesOrig As String, sFieldNamesTarget As String

    Dim oApp As Excel.Application      'Excel Application
    'dim oApp as Object    'you can remove the reference to Microsoft Excel xx.0 Object library)
    Set oApp = CreateObject("Excel.Application")     'opens new instance of Excel
    oApp.Visible = False            'for testing, you can see what Excel does, set to false when done
    'Dim oWkbk As Object      'defines a workbook
    Dim oWkbk As Excel.Workbook
    oApp.DisplayAlerts = False            'turn off alerts
  
    msgApp = "Certified Payroll"
    
    'Extract Unique Jobs
    strCriteria = "[Date] Between " & Format(dteStart, strcJetDate) & " And " & Format(dteEnd, strcJetDate)  ' Setup Date Criteria
    Set rsJobs = CurrentDb.OpenRecordset("SELECT DISTINCTROW Job FROM tblPWBenefits WHERE (" & strCriteria & ") GROUP BY Job ORDER BY Job;", dbOpenDynaset)
    
    lRecCount = rsJobs.RecordCount
    If lRecCount = 0 Then
        msgApp = "Certified Payroll"
        msgText = "No Jobs within that date period - Procedure Stopped"
        Response = MsgBox(msgText, vbOKOnly, msgApp)
        Exit Sub
    End If
    
    Dim FileName As String
    Dim filenamePDF As String
    Dim directoryName As String
    
    sDate = Format(dteStart, "mmddyyyy")
    dashdate = Format(dteStart, "mm-dd-yyyy")
    directoryName = gExportPath & "\" & dashdate
    
    'Make directory
    If Not DirExists(directoryName) Then
        MkDir (directoryName)
    End If

    TempVars.Add "StartDate", dteStart
    TempVars.Add "EndDate", dteEnd
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
        
    Do While Not rsJobs.EOF
        Job = rsJobs!Job
        
        'Set up from/to for Job Selection
        ''TempVars.Add "Job", Job                       'for qryIllinoisPWExportPortal
        ''TempVars.Add "JobTo", Job                    'Not being used, I don't think
        
        TempVars.Add "JobStart", Job                  'for qryIllinoisPWExport
        TempVars.Add "JobEnd", Job
        
        FileName = directoryName & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv"
        
       ' DoCmd.TransferText acExportDelim, "QryIllinoisPWExportPortal Export Specification", "qryIllinoisPWExportPortal", filename, True
        DoCmd.TransferText acExportDelim, "qryIllinoisPWExport Export Specification", "qryIllinoisPWExport", FileName, True

         'Rename Columns
        sFieldNamesOrig = Me.FieldNamesOriginal
        sFieldNamesTarget = Me.FieldNamesTarget
        sFile = FileName
        
        'replace original with target
        Call TextFile_FindReplace(sFile, sFieldNamesOrig, sFieldNamesTarget)
        
        'Use Excel to save file in format IDOL Portal wants
        With DoCmd
            .SetWarnings False
             Set oWkbk = oApp.WorkBooks.Open(sFile)
             oWkbk.SaveAs sFile
             oWkbk.Close
            .SetWarnings True
        End With
              
        filenamePDF = directoryName & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.pdf"

        With DoCmd
            .SetWarnings False
            .OutputTo acOutputReport, "rptAffidavit", acFormatPDF, filenamePDF
            .SetWarnings True
        End With
        DoCmd.Close acOutputReport, "rptAffidavit"             '3-2-22
        rsJobs.MoveNext
    Loop
    Set qdf = Nothing
    Set dbs = Nothing
    Set rsJobs = Nothing
   ' dbs.Close              '3-2-22   error message
  '  rst.Close              '3-2-22   error message
   ' rsJobs.Close           '3-2-22
    
     'Close Excel
    Set oWkbk = Nothing
    oApp.DisplayAlerts = True
    Set oApp = Nothing
    'oApp.Close              '3-2-22
    
    TempVars.RemoveAll
    
    msgText = "Extract Completed, look at folder " & gExportPath & " for extracted CSV files"
    Response = MsgBox(msgText, vbOKOnly, msgApp)
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 28, 2001
Messages
23,166
You are looking for a coding error that causes bloat, but there might not be an error. Problems with bloat are functions of having to create an output or transfer list that will not be kept, but rather will be deleted when done. It is not possible to actually clean up (reclaim) that "sacrificed" chunk of memory until you do a Compact & Repair OR... If this is strictly an FE problem and there is ANY way to do this, you can use an external launcher script to copy a "virgin" FE file to your local work area and launch that new copy each time you need to do this report cycle. As long as you don't have any persistent data in the FE, you can kill the bloat every time by just replacing the file every time.
 

AngelSpeaks

Active member
Local time
Today, 17:46
Joined
Oct 21, 2021
Messages
291
Thanks Doc. I was thinking of having to replace the FE. I created an accde right away because I found out that once it started bloating, I couldn't create one. I'm now testing on the accde so I can easily replace it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:46
Joined
May 7, 2009
Messages
16,826
use IBM, absolutely no bloat!
 

Users who are viewing this thread

Top Bottom