locked
database is growing fast ? Querydef ? RRS feed

  • Question

  • I inherited this code, and it seems the database is growing rather fast when using this routine .

    i'm not familiar with querydef , can it have something to do with that

     

    Dim sqlstring As String
    Dim fnr As Integer

    On Error GoTo ErrorHandler

    If IsNull(Me.[commenu]) Then
        MsgBox ("choose to copy ")
    Else
        DoCmd.Hourglass True
        Set db = CurrentDb()
        db.QueryDefs![qsubtractmenu].Parameters![PARFEEST] = Me.FSNO
        db.QueryDefs![qsubtractmenu].Parameters![PARMENU] = Me.commenu
        db.QueryDefs![qsubtractmenu].Parameters![PAR3fvolgnr] = Me.fvolgnr
        db.QueryDefs![qsubtractmenu].Parameters![PARAANTAL] = Me.faantal
        db.QueryDefs![qsubtractmenu].Execute

        Me.Parent.Sub163.Form.Requery
        Me.Parent.menut = True

         Requery
        DoCmd.Hourglass False
        If Me.Recordset.RecordCount > 0 Then Me.Recordset.MoveLast
    End If

             
    Exit_Sub:
    Exit Sub

    ErrorHandler:
    MsgBox Err.Description
    Resume Exit_Sub

     

    Friday, August 26, 2016 9:11 AM

Answers

  • You cannot control the size of the FE, and these small increases are normal, so stop worrying about it. 90 MB is not large. I would get worried if it ballooned to 500 MB.

    -Tom. Microsoft Access MVP

    • Marked as answer by tekoko10 Saturday, August 27, 2016 3:40 PM
    Saturday, August 27, 2016 2:12 PM
  • One large factor and setting will be if you turned on row locking as opposed to page locking.

    A great example is this simple code to edit (modify) 30,000 records in a table:

       Set rst = CurrentDb.OpenRecordset("contacts")
    
       t = Timer
    
       Do While rst.EOF = False
          rst.Edit
          i = i + 1
          rst!City = "ci " & i
          rst.Update
    
          rst.MoveNext
    
       Loop
    
    

    The data file is about 6.7 megs in size (quite small). When I run the above, there are 30,000 records – it runs in LESS then one second (0.098 seconds). The file size AFTER the above code shows no change. (6.7 megs.

    Now, turn on row locking. Run the above SAME code.

    Two ASTOUNDING things are noted.

    Execute time is OVER double (twice as slow).

    The file size grows to 127 megs in size (an increase of 119 megs).

    So the file bloat and increase is astounding in regards to row locking.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by tekoko10 Wednesday, August 31, 2016 9:22 AM
    Tuesday, August 30, 2016 10:30 PM

All replies

  • What is the SQL Statement behind qsubtractmenu

    "database is growing rather fast"

    From what size to what size?  What do you call growing rather fast?


    Daniel Pineault, 2010-2015 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, August 26, 2016 1:03 PM
  • Maybe your copy failed. Requery alone cannot work. It needs an object like Me.Requery
    You inherited this code. It's sloppy programming, is this a beginner?

    I suggest you rewrite the code and make it robust ( Me.Parent.Sub163 is not good programming practice) and also properly comment it.


    Best regards, George


    Friday, August 26, 2016 2:51 PM
  • it is about 77.000 Kb, after 1 day using  by about 5 users , it is 90.000 kb

    Friday, August 26, 2016 4:08 PM
  • Hi George ,

    yes , it was a beginner and it was nearly 8 years ago he made it. the database design was terrible.

    The problem is a have a user (74 old) that owns the company and does not want to see his programs (forms) to change very much  ..... the most easy way was to start from scratch...

    it was even a 2003 database ... so what I do is step by step changing the things in the background , but sometimes I see things I  don't understand :):)  like this one , and i'm afraid to change too much at once, to avoid errors .

    I must say it is a very good and busy company, so I can not make too many mistakes together.

    Friday, August 26, 2016 4:19 PM
  • the sql statement is

    INSERT INTO Menu ( FSNO, GRCHNO, MNaantal, MNvolgnr, mnonderdeel, mneenheid, mngerecht, mnmateriaalnodig, mnmateriaalpp, invoerdatum, mngerechtep, mngerechtprijs )
    SELECT [PARFEEST] AS F0, qmenu1.GRCHNO, [MNaantal]*[PARAANTAL] AS F1, [MNvolgnr1]+[PAR3fvolgnr] AS MNvolgnr, qmenu1.mnonderdeel, qmenu1.mneenheid, qmenu1.mngerecht, qmenu1.mnmateriaalnodig, qmenu1.mnmateriaalpp, qmenu1.invoerdatum, qmenu1.mngerechtep, qmenu1.mngerechtprijs
    FROM qmenu1 LEFT JOIN qmenu2 ON qmenu1.GRCHNO = qmenu2.GRCHNO
    WHERE (((qmenu2.GRCHNO) Is Null));

    where qmenu1 is

    SELECT Menu.FSNO, Menu.GRCHNO, Menu.MNaantal, Menu.MNvolgnr AS MNvolgnr1, Menu.mnonderdeel, Menu.mneenheid, Menu.mngerecht, Menu.mnmateriaalnodig, Menu.mnmateriaalpp, Menu.invoerdatum, Menu.mngerechtep, Menu.mngerechtprijs
    FROM Menu
    WHERE (((Menu.FSNO)=[PARMENU]));

    and qmenu2 is

    SELECT Menu.FSNO, Menu.GRCHNO
    FROM Menu
    WHERE (((Menu.FSNO)=[PARFEEST]));

    basically it copys ready-made menus that are stored in the file menu under a specific customernr  into the same file menu with another FSNO   (party nr) 

    Friday, August 26, 2016 4:26 PM
  • There you go, it's an

    INSERT INTO

    append query so it is adding new records to the table.  Depending on the frequency of its use this can make your db grow.

    77Mb ->90Mb isn't a huge change though.  And this is the back-end file size, right?


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Friday, August 26, 2016 4:58 PM
  • It is a accde file
    Friday, August 26, 2016 7:03 PM
  • Maybe your copy failed. Requery alone cannot work. It needs an object like Me.Requery


    Actually, Requery alone in a form's class module will work.  I think that's because Requery is a method of the form class, so without other qualification it would be the equivalent of Me.Requery.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 26, 2016 8:31 PM
  • If it's an ACCDE then I think you are talking about the front-end and not the back-end. The front-end db may expand a bit initially and then should stabilize. Does each user have their own copy of the front-end or are they all sharing a common copy?


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Friday, August 26, 2016 11:27 PM
  • it is expanded , and after compiling it gets back approximately the original size , so that's why I thing it grows too much
    Saturday, August 27, 2016 7:34 AM
  • You cannot control the size of the FE, and these small increases are normal, so stop worrying about it. 90 MB is not large. I would get worried if it ballooned to 500 MB.

    -Tom. Microsoft Access MVP

    • Marked as answer by tekoko10 Saturday, August 27, 2016 3:40 PM
    Saturday, August 27, 2016 2:12 PM
  • ok , thanks Tom , i'll stop worrying :)

    Saturday, August 27, 2016 3:40 PM
  • One large factor and setting will be if you turned on row locking as opposed to page locking.

    A great example is this simple code to edit (modify) 30,000 records in a table:

       Set rst = CurrentDb.OpenRecordset("contacts")
    
       t = Timer
    
       Do While rst.EOF = False
          rst.Edit
          i = i + 1
          rst!City = "ci " & i
          rst.Update
    
          rst.MoveNext
    
       Loop
    
    

    The data file is about 6.7 megs in size (quite small). When I run the above, there are 30,000 records – it runs in LESS then one second (0.098 seconds). The file size AFTER the above code shows no change. (6.7 megs.

    Now, turn on row locking. Run the above SAME code.

    Two ASTOUNDING things are noted.

    Execute time is OVER double (twice as slow).

    The file size grows to 127 megs in size (an increase of 119 megs).

    So the file bloat and increase is astounding in regards to row locking.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by tekoko10 Wednesday, August 31, 2016 9:22 AM
    Tuesday, August 30, 2016 10:30 PM