Serious bloating

[NigelShaw]

Hi,

my db (not holding any data) has bloated to 130mb. i created a new db and imported all of the objects needed. This was then a healthy 250kb. 10 mins after it has been open, its 130mb again......


any ideas?

i have about 20 records in it so its empty
there are about-

20 forms (all with code but simple enough code)
30 queries (not difficult ones either)
15 modules (approx 100 routines)
15 tables (currently holding not a lot)

i have 3 flx grids on separate forms
i have 1 treeview on a separate form.

i am using A2007 on windows 7


a bit of advice please?




cheers

Nigel

 

[Banana]

Are you creating any temp tables? Creating any controls/forms/reports at runtime?

 

[NigelShaw]

Hi Banana

No I'm not. All of my controls like the flexgrid and treeview are early bound and referenced. I did look back through my revisions and I had a steady 250kb right up until 2 days ago. At that time-

I made a form bigger but bit didn't add anything
Adjusted some existing code to make the grid cells fit better

I also added my treeview at this time. There is a lot of code involved as it makes the nodes and sorts them at runtime. I took it form the sample left in my previous post but that itself, isn't very big so I can't why it would start to bloat in a new db?

All tables are existing.

I'll try a new db again with everything except the treeview but to be honest, I need it in..


Cheers

N

 

[NigelShaw]

Hi Banana

i have just read that non queryDef SQL can be major factor in bulging. As previously stated in a separate thread about SQL, i have a lot of varied types of queries going on. I say varied as some work and others dont. for example-

CurrentDb.Execute "INSERT INTO tblTimeSheets

works just fine in a flexgrid but not in a treeview!

i can run a query made in the builder just fine but if i reference it via code, i get 'Too Few Parameters' issue

so, i have DAO, .Execute, DoCmd.RunSQL all going on and i have a few recent entries of QueryDefs.

What is the best most effective and guarenteed way of working with queries to potentially stop bulging?


Cheers


Nigel

 

[Banana]

I do not believe using dynamic SQL, whether via Database.Execute or DoCmd.RunSQL method create the bloat. It's mainly related to creating objects because by design, when Access needs more storage to hold data (whether temporary or not), it allocate the extra space on drive but when it's done with data, it doesn't release the allocated space until next Compact & Repair.

As for your reported problems with Execute, this is because you cannot do this:

CurrentDb.Execute "INSERT INTO myTable VALUES ([Forms]![MyForm]![Mytextbox]);",dbFailOnError

This won't work because you're operating at a lower level of the hierarchy - the database engine has no awareness of any open forms or controls. You see, it's the Access layer that resolves the references such as "[Forms]![MyForm]![MyTextbox]" into a literal value such as "TextITyped". DoCmd is a part of Access object model and thus is "aware" and will automatically resolve the references for you. But when you're using Execute method, you have to resolve the reference yourself like thus:

CurrentDb.Execute "INSERT INTO myTable VALUES (" & Me.MyTextbox & ");", dbFailOnError

(assuming the code is running in the module behind the same form. I know some may opt to use this variant to ensure that it'll work anywhere regardless of how it's executed:

INSERT INTO myTable VALUES (Eval("[Forms]![MyForm]![MyTextbox]"));

By invoking Eval(), the database engine can "ask around" and thus resolve the reference with help from Access. My personal preference is to avoid Eval() because it's too dynamic for my tastes but well, there you go.

 

[NigelShaw]

Hi Banana,

here are some of my methods-

DAO

sSQL = "SELECT dbo_QuotationItems.ItemId, dbo_QuotationItems.Description, dbo_QuotationItems.Quantity, " & _
    "dbo_QuotationItems.UnitCost, dbo_QuotationItems.UnitPrice, dbo_QuotationItems.TotalCost, dbo_QuotationItems.TotalPrice " & _
    "FROM dbo_Quotation " & _
    "INNER JOIN dbo_QuotationItems " & _
    "ON dbo_Quotation.QuotationID = dbo_QuotationItems.QuotationId " & _
    "WHERE (dbo_Quotation.reference = '" & strReference & "' AND ((dbo_Quotation.Revised)=False));"

Set db = CurrentDb
    Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)

QueryDef

Public Sub LoggedIn(pstrLoggedIn As String, InOut As Boolean)

    Const cstrQueryName As String = "qryEmpLogIn"

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    Dim strSQL As String
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(cstrQueryName)
    'db.QueryDefs.Delete cstrQueryName
    'Set qdf = db.CreateQueryDef(cstrQueryName, strSQL)
    qdf.Parameters("LoginName") = pstrLoggedIn

    Set rs = qdf.OpenRecordset()
    
    
    rs.Edit
    rs!LoggedIn = InOut
    rs.Update

    rs.Close
    qdf.Close
    db.Close

    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Sub

Recordset

Private Sub psOpenRecordset(strSQL As String)
'Open the records that match the search
    
    psCloseRecordset
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            
End Sub

strSQL = "SELECT * FROM (" & strCoreSQL & ") As vTbl" & _
    " WHERE " & strFilter & _
    " ORDER BY SectionPath"

    'Open the records
    psOpenRecordset strSQL

.Execute

strSQL = "INSERT INTO Sections" & _
                " (BelongsTo, SectionName, CreatedBy)" & _
             "VALUES (" & IIf(strParentKey = tv_conRootBelongsTo, "Null", ParseKey(strParentKey, 0)) & _
                     ",'" & cStrNodeText & "','" & Environ("Username") & "')"

    On Error Resume Next
    With CurrentDb
        .Execute strSQL, dbFailOnError
        lngSectionID = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
    End With

i just dont understand why some of these work whilst others need a different method. i dont believe i could swap the methods around and they would still be successful. Oddly enough, the DAO version doesnt have a qry and the SQL is totally a string however, if i simulate this in the query builder and run it, it works fine. if i construct a stribf and call the query through DAO, i get a Parameter fail...

whats the deal?



Cheers

Nigel

 

[Banana]

I see that you've commented out the CreateQueryDef & QueryDefs.Delete. That would have definitely created bloating. If there's still other like that, you'll want to comment that, too. It's OK to change SQL property of an existing QueryDef, but generally avoid CreateQueryDef.

DAO does have a qry - it's QueryDef. As for parameters, I find that it's also good idea to be explicit by declaring them beforehand. You do this like thus:

PARAMETERS myParam AS TEXT, myOtherParam AS LONG;
SELECT ...
FROM ...
WHERE ... = [myParam] AND ... = [myOtherParam];

By making the declaration explicit, it removes some ambiguity and reduce the likelihood that it'll fail. Beyond that, the explanation about resolving references applies.

 

[NigelShaw]

Hi Banana

Why is it the that you can reference a form to a filtered query and it works just fine but reference a dao recordset and you parameter issues only sometimes? Some dao recordsets can look up a filtered query and others cannot.

Recently I made a small db for my wife as she needed to get certain data out of her till software. It all runs on SQL so I made a filtered for allowing lots of filter options. All works fine without queryDefs but in the current project using the same method and some extent, the same code but less filtered, it produces the parameter error. This is why o mentioned in a separate post about it being flakey. There doesn't seem to be consistency

Am I better off adding a parameter to every query I do and setting that in the code with the parameter being the criteria?


Cheers

Nigel

 

[Banana]

You'll need to provide an example of what you're describing because AFAIK the behavior of Database.Execute & Database.OpenRecordset and DoCmd.RunSQL & DoCmd.OpenQuery is pretty much consistent once you take in the differences of how they resolves the references to Access objects.

 

[NigelShaw]

Hi Banana,

getting back to my post. i was going to post but its currently too big!! It was 250kb when i started this message but the oddest thing.

i created a new db (A) and imported everything into it
the size was 250kb
i copied the db (A) and left the copy (B) untouched and unopened
i opened up the db (A) and did a few very small things.
i closed it and its size was 13mb
the unopened and untouched db also changed its size to 13mb too

Something must be bloating this... the original sample doesnt bloat? i havent made many changes to it. the changes made-

re-designed the form to suit what i need
removed the combo box for adding items and replaced it with 3 buttons
removed a listbox that was used for searching

can someone have a quick look at all please? this is a DatAdrenalin db


cheers


Nigel

 

[Banana]

I don't see a link? Have you tried zipping it up?

 

[NigelShaw]

Hi Banana,

its zipped

cheers

Nigel

 

[Banana]

I've traced it to simply opening the frmParts. However, I had problem with tinkering with the frmParts. For starters, I got an error:

438
Object doesn't support this property or method

on this line:

    Forms!frmParts!LstFlxGrd.Redraw = False

for the procedure FillLstGrid.

Furthermore, if I try to delete or do anything with the Listgrid control I get an error saying it cannot be deleted due to my security setting, despite it being trusted and all that.

What i wanted to do is to see if deleting the Listrid control makes the bloat go away.

 

[NigelShaw]

Hi Banana,

you get that message if you dont have MSFlexGRID.OCX referenced. When the form opens, the tree is loaded and then the routine FillLstGrid will load up the grid that shows the contents of the DBO_QuotationITemDetail via a query which is filtered to the SectionIDPath of the treeview.

the idea is

the treeview loads the tree
the grid only shows the record related to the tree object

if you dont have msflexgrid, it is attached. it goes into the System32 folder and will need to be registered.

if the flexgrid is not referenced, the code will fail on this line-

Forms!frmParts!LstFlxGrd.Redraw = False

as it is the first call to the LstFlxGrd object on the form. I'll delete it my side too to see if it helps

cheers

Nigel

 

[NigelShaw]

Here is a fresh db with the flexgrid form object removed and the routine calls turned off

size is currently 800kb


cheers

Nigel

 

[Banana]

Here's what I did to "trace" the bloating.

Create a new form, put one textbox and set Popup=yes. Clear out all other properties (Record Selector, Navigator etc etc) so it's more like a dialog.

Set an timer interval of 1 and add an timer event:

Me.Text0 = FileLen(CurrentDb.Name)

When you open it, it'll report the size of the file in bytes. I find that the increase occurs between double-clicking the frmParts and before form's Load event executes, so it's not your code that's causing the bloat. Remove the MSFlexGrid and bloat doesn't increase as much.

 

[NigelShaw]

Hi Banana,

since removing the grid, it doesnt reallt bloat. its stayed consistently at 808kb for many changes, data updates and open / closing. Its odd though as i have a flexgrid on may forms and in some cases, 2 or 3 grids on the same form without any bloating so why this?

i'll try and add a new one from scratch.

at this stage, i'd rule out the code too as its the same code i always use even down to the calculation of the column widths to fit.

when the form opens, it calls the FillLstGrd to fill the grid. When a node is clicked, it recalls FillLstGrd to refresh the grid. this would be the only possible thing i could think off but, it is the right method to refresh the grid and again, ive used it many times before


i'll investigate further


cheers

Nigel

 

[NigelShaw]

Hi Banana

My pc is away for the night now but I did have A thought. Would commandbars create gloating if not handled correctly? I recall trying to add a shortcutmenu to the tree sp must have put the code in. Also, I have 2 menus on my ribbon that I didn't add and should be there.

Is there a way of looking through the commandbars without specifying names and deleting them

Cheers,

Nigel

 

[datAdrenaline]

Hey ... that code looks familiar! .. ..

----

I used your db in A2010. The db size stayed right at 13mg after about 20 opens and closes ... with the FlexGrid and me selecting different nodes on the Tv.

----

Also ... with respect to the side bar discussion about the difference between DoCmd.RunSQL and CurrentDb.Execute ...

Check out the following post on UA and see if if helps explain it at all:
http://www.utteraccess.com/forum/simple-t1228050.html#entry1229035

 

[NigelShaw]

Hi Dat

I had mentioned further up that it's from your db

I did wonder that it maybe the reference was to blame? I thought this because when I made a new one and imported everything, I closed it and made a copy. The size was 800kb. I then opened it and closed it again, both db's had bloated even though one of them had never been opened!

The db started of at 800kb and should never be 13mb for such a small db? What do you think?

I don't think it's the code at fault.


Cheers

Nigel