Results 1 to 13 of 13
  1. #1
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6

    Access database bloating

    I have a database in MS Access, split on front-end and back-end. There is a whole lot of inserting and updating going on by multiple users. Problem is, my database is bloating (rapidly increasing in size). By rapidly, I mean every hour 100-200 MB increase in size. I closed all DAO objects with which I worked in code which isn't the problem, I think. When I have frontend open only on my computer and I am inserting into database it doesn't grow at all (1500 inserts = 1 MB). I think this has something to do with record level locking but I can not figure out what is the problem really. I turned of row record level locking in my database and .laccdb is still showing, database is still bloating. Also, I am opening all recordsets in dbReadOnly mode.
    Do you have any suggestions?


  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    I presume you are talking about the back end. Bloat is caused by creating/deleting objects (i.e. tables) and storing things like pictures and OLE objects. Recordlocking should not have any significant impact.

  3. #3
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    Yes, I'm talking about back end. I am not deleting or creating any objects, neither storing any pictures/OLE objects. Just inserting and updating records in existing tables.
    Can it be that creating many DAO.Recordsets and deleting them is causing such increase in size?

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    OK what is the impact of the file size if you compact/repair the back end - size before, size after?

    Can it be that creating many DAO.Recordsets and deleting them is causing such increase in size?
    no, recordsets are created in the front end, not the back end

  5. #5
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    So in one day it goes from 207 MB to 1 GB, when compacted it goes to 208 MB.

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    in that case, sounds like there is an issue with your inserting and updating routines. What is the process you are actually using?

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    Also - I presume each user has their own copy of the front end? If they are sharing the front end, that can cause corruption in the back end (and front end for that matter) which might just result in bloat. the reason for asking is you say

    When I have frontend open only on my computer and I am inserting into database it doesn't grow at all
    so what about the other users?

  8. #8
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    Set rs = mydb.OpenRecordset(sql, dbReadOnly)

    With rs
    Do Until .EOF
    sql = "Inserting/Updating tables with data from rs"
    mydb.Execute sql
    .MoveNext
    Loop
    End With

  9. #9
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    Quote Originally Posted by Ajax View Post
    Also - I presume each user has their own copy of the front end? If they are sharing the front end, that can cause corruption in the back end (and front end for that matter) which might just result in bloat. the reason for asking is you say

    so what about the other users?
    Yeah, each user has their own copy of front end on their Desktop.
    I copied main front end on every Desktop.

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    I have to go out now, but your code is not sufficient to explain the process - where does the data come from in the first place? explain the process.

    Only other thought is if you are over indexing - if you update an indexed field that requires the index 'record' to be deleted and reinserted.

    It might be recordlocking, although I've not seen it as an issue myself - see this link https://stackoverflow.com/questions/...database-bloat - but looks like you did that - presumably in the back end?

  11. #11
    hrastic is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    I have somewhat of a chain reaction insert:
    1. Insert into one table
    -> for example:
    Insert a basketball player and his height
    INSERT INTO Table(Name, Height(cm)) VALUES('Lebron James', 206);
    2. Create a query that gets other type of information from that insert (query creates multiple records from one insert)
    Query gets me which positions can he play on field
    Player_ID Role
    1 PG
    2 SG
    3 SF
    .............
    3. Select from that query and update info in another table

    So, that with the code I provided should be enough, I think.
    So all I am doing is really inserting and updating, afterwards close all DAO objects. I really don't know what is the reason of rapidly increasing size.

  12. #12
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    I'm out of ideas - I don't understand how your tables work and can't get the context with multiple users and entering players or why you get data from one table to store or update in another. The fact it compacts back to the same size or close to and is not a problem for you as a user but is for everyone else is also confusing. Suggest you go to each users machine in turn and test for bloat, perhaps one or two have a slightly different front end version causing the issue.

  13. #13
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    54
    Here is an article that hopefully might be helpful in your situation...

    How to prevent database bloat after you use Data Access Objects (DAO) - 12/09/2019
    https://docs.microsoft.com/en-us/off...base-bloat-dao

    And here is a snippet of the text...
    <<
    Cause
    If you do not release a recordset's memory each time that you loop through the recordset code, DAO may recompile, using more memory and increasing the size of the database.

    Resolution
    To avoid consuming unnecessary resources and increasing database size, use the Close method of the Recordset object to explicitly close the recordset's memory when you no longer need the recordset.
    >>
    J. Paul Schmidt - Freelance Web and Database Developer
    Sample Database on the Web
    Sample Access Database

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-31-2018, 12:16 PM
  2. Replies: 6
    Last Post: 08-08-2016, 06:42 AM
  3. Replies: 4
    Last Post: 05-18-2015, 09:36 AM
  4. Database Bloating Problem
    By jfriedm in forum Access
    Replies: 4
    Last Post: 10-10-2012, 08:04 AM
  5. Linked OLE Objects bloating size of database
    By Nosaj08 in forum Database Design
    Replies: 18
    Last Post: 07-09-2009, 05:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums