Access VBA Coding Best Practices: File Fragmentation

Experts:

I near completing a project I created in Access and now going back to clean up my code and tie up loose strings.  One thing concerns me about my project is that the size of the MDB gets incrementally larger as I use it without adding records to any tables.  

My project is form driven, utilizing many sql calls in vba from 12 or so tables.  I have Microsoft Chart Objects on my form which, when triggered, is updated via sql string.  I have two sub forms, both of which display one table each in datasheet view.

When I close my file it is around 5.5mb.  After clicking "repair and compact" the size of the file is reduced by 50%.   When I open the file again, and move through some of the forms I find the size grows all the more.  This project will be utilized by many end-users and I want to ensure the integrity of the project (for the most part).

Where should I begin to look for common errors that add to, what appears to be, data fragmentation?  

The attached code is just a snippet of my project though the methods are pretty consistent.



Private Sub cmdAdd_Click()

Dim i As Integer
Dim sumAmt, sumScore, allocAmt, portAllocAmt, portAllocRem, portSize, sumPct As Double
Dim frmProdType, varProdName, frmRiskType, mySqlStr, frmInvObjective, frmOffice, frmIOCode As String
Dim qdfNew, New_Query As QueryDef
Dim db As Database
Dim rs As Recordset

allocAmt = lblAmtAlloc.Caption
portSize = lblPortSize.Caption
frmInvObjective = txtInvObjective.Value
frmOffice = txtOffice.Value


mySqlStr = "Select product_code, product_name, alloc_amount from tblTempProd "
Set qdfNew = CurrentDb.CreateQueryDef("New_Query", mySqlStr)
sumAmt = DSum("Alloc_Amount", "New_Query")
CurrentDb.QueryDefs.Delete ("New_Query")

If IIf(IsNull(sumAmt), 0, 1) = 0 Or sumAmt = 0 Then
    MsgBox ("Nothing to Add"), vbInformation
    Exit Sub
End If

If allocAmt - sumAmt > 0 Then
    MsgBox "Hello World", vbInformation, "Hello"
    lblAmtRem.Caption = Format(sumAmt - allocAmt, "Currency")

    Else
    
    If allocAmt - sumAmt < 0 Then
    MsgBox "Good bye world", vbInformation, "Hello"
    lblAmtRem.Caption = Format(sumAmt - allocAmt, "Currency")

    Else

    End If

End If

        Set db = CurrentDb()
        
        Set rs = CurrentDb.OpenRecordset("Select INVS_OBJ_CD from tblIOS as IO " & _
            "WHERE IO.INVS_OBJ_DESC = '" & frmInvObjective & "' and IO.OFFICE = '" & frmOffice & "' ")
        frmIOCode = rs(0)
        rs.Close
        
        'V==Clean duplicates
        db.Execute ("Delete * " & _
        "FROM tblTempProdAlloc as tpa " & _
        "WHERE tpa.PRODUCT_NAME in (SELECT tp.PRODUCT_NAME FROM tblTempProd as tp) "), dbFailOnError
        
        'V==Insert Selection
        db.Execute ("insert into tblTempProdAlloc (Asset_Class_Code, Asset_Class, Asset_subclass_code, Asset_Subclass_Desc, " & _
            "Product_code, Product_name, alloc_amount, aa_percent, wtd_risk, strat_percent, risk_score) " & _
            "SELECT subc.Asset_class_code, aclss.Asset_class_desc, tp.asset_subclass_code, subc.asset_subclass_desc, " & _
            "tp.product_code, tp.product_name, tp.alloc_amount, tp.aa_percent, tp.wtd_risk, (aa.alloc_target*100), tp.risk_score " & _
            "FROM tblTempProd AS tp, tblAssetClass AS aclss, tblAssetSubclass AS subc, tblAssetAllocation AS aa " & _
            "WHERE subc.asset_subclass_code=tp.asset_subclass_code And aclss.asset_class_code=subc.asset_class_code " & _
                "And aa.asset_subclass_code=subc.asset_subclass_code and aa.INVS_OBJ_CODE='" & frmIOCode & "' and tp.alloc_amount >0 "), dbFailOnError
        
        db.Close
        Set rs = Nothing
        
        lblAmtRem.Caption = Format(sumAmt - allocAmt, "Currency")
        Forms!frmProductAllocation!subfrmFinalAlloc.Form.Requery

'<>=== Asset Allocation Test
mySqlStr = "Select alloc_amount from tblTempProdAlloc "
Set qdfNew = CurrentDb.CreateQueryDef("New_Query", mySqlStr)
sumAmt = DSum("Alloc_Amount", "New_Query")
CurrentDb.QueryDefs.Delete ("New_Query")

mySqlStr = "Select aa_percent from tblTempProdAlloc "
Set qdfNew = CurrentDb.CreateQueryDef("New_Query", mySqlStr)
sumPct = DSum("aa_percent", "New_Query")
CurrentDb.QueryDefs.Delete ("New_Query")

Set db = CurrentDb()

db.Execute (" Delete * from tblTempProdScores "), dbFailOnError

db.Execute (" INSERT INTO tblTempProdScores (PRODUCT_CODE, ASSET_SUBCLASS_CODE, RISK_TYPE, RISK_SCORE, WTD_RISK_SCORE) " & _
"SELECT p.product_code, p.asset_subclass_code, p.risk_type, p.risk_score, (p.risk_score*(pa.aa_percent/100)) " & _
"FROM tblBudgetMaster AS p, tblTempProdAlloc AS pa " & _
"WHERE p.product_code=pa.product_code "), dbFailOnError

mySqlStr = "Select wtd_risk_score from tblTempProdScores "
Set qdfNew = CurrentDb.CreateQueryDef("New_Query", mySqlStr)
sumScore = DSum("wtd_risk_score", "New_Query")
CurrentDb.QueryDefs.Delete ("New_Query")

db.Close

lblPortRiskScore.Caption = Format(sumScore, "0.0")
lblPortAllocAmt.Caption = Format(sumAmt, "Currency")
lblPortAllocRem.Caption = Format(portSize - sumAmt, "Currency")
lblPctAllocated.Caption = Format(sumPct / 100, "0.0%")

lstNotAllocated.Requery

End Sub

Open in new window

donisanpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
RE:

insert into tblTempProdAlloc

If you are inserting records into a temp table in the front end ... then this will guarantee bloating.  Is this what you are doing?

mx
0
Rey Obrero (Capricorn1)Commented:
if deleting and appending records in inevitable, there are two ways you can reduce the size of the front end
  • tools > option > general, check Compact on close
  • you can write vba codes to compact and repair the db when it reach certain size

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
donisanp

Are you familiar with 'splitting' your app into a front end and back end?  If not ... see this:

'How to manually split a Access database in Microsoft Access
http://support.microsoft.com/kb/304932

http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/Index.html

Splitting falls right in with good coding practices.  Further, if you give each user their own copy of the front end (and you should) ... and copy the FE master from the server each time a user needs to use the database ... bloating in the FE basically becomes a non-issue ... because it will get replaced each time.   And to help you with this concept ... see this:

Auto Updater:  http://www.granite.ab.ca/access/autofe.htm

mx
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

donisanpAuthor Commented:
MX - yes, I use temp tables as subforms on my main form.  The problem I have is that the number of products varies and I need to be able to display all relevant products simultaneously to make my purchase decision.  Based on trial and error, this approach is most fluid from a client experience approach.  

As for splitting the db, it is already split.  The temp tables are cleared when the user exits the project.  Is the bloating normal even when no new data is being added to the project?

Capricorn - I've thought about checking the "compact on close" box.  I've heard that doing so may corrupt the project, is this true?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"As for splitting the db, it is already split.  "
Does each user have a separate copy on their workstation ?

" The temp tables are cleared when the user exits the project. "
Unfortunately, the data is not deleted internally until a Compact & Repair is done .... thus the bloating effect.

But ... nothing wrong with using temp tables.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
< I've thought about checking the "compact on close" box.  I've heard that doing so may corrupt the project, is this true?> definitely NO
0
donisanpAuthor Commented:
MX - Yes, each user has (or eventually will have) a copy on their desktop.  The meat of the data comes from a separate database via linked tables.

Capricorn - thank you for confirming that compact on close does not corrupt the project.


Verdict: Compact on close.
0
donisanpAuthor Commented:
Gentlemen - thanks again for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.