Results 1 to 14 of 14
  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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    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
    64
    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.
    >>

  14. #14
    jcoogan is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    1

    Another possible cause of Access Database Bloat

    Quote Originally Posted by hrastic View Post
    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?
    Here's another possibility that took me forever to discover. I am using Microsoft 365 subscription, ver 2302. Check menu: "File, Options, Current Database". Look for "Track name Autocorrect info" and uncheck it.


    I >think< the idea behind this option is that Access will auto correct other instances of objects behind the scenes when you change an object name. Example: You have two queries that are linked and you change the name of one, then Access will automagically update the name change in the separate join query, preventing it from breaking. Pretty cool, but also possibly pretty expensive in terms of database bloat because Access has to write all these object references somewhere!


    In my case I have a database with code only, data tables are in a separate linked database. The code database was bloating from 3000k to the 2gig limit in 20 min! My code database is doing a lot of database object and recordset object creation and deletion. When I unchecked the option named "Track name Autocorrect info" it resolved the issue and now the code database only grows by about 500k.

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
  •  
Other Forums: Microsoft Office Forums