Results 1 to 13 of 13

Incongruous database file sizes

  1. #1
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22

    Incongruous database file sizes

    I have two databases of similar structure and similar size, in terms of the numbers of records. However, the file size of one is about eight megabytes while that of the other is in excess of two gigabytes. I tried "Compact and Repair" on the latter but it made no difference.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Access file size limit is 2GB so don't see how can be in excess of 2GB.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22
    More precise figures, using File Explorer notation: 8480 KB and 2064512 KB.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Suggest you try importing tables into a new db to see what happens.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22
    I created a new blank database (Database1) and tried to import into it all the tables, queries, forms, reports and modules from my existing anomalous database. The process failed part way through but by the time this happened two files had been created vis Database1.accdb and
    Database1_Backup.accdb. So far as I could see they contained the same set of tables (and nothing else) but the size of the former was 608 KB while that of the latter was 1305244 KB.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    Suggest you import 1 table at a time and check file size after each.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22
    Quote Originally Posted by June7 View Post
    Suggest you import 1 table at a time and check file size after each.
    Thanks, I tried that and it did enable me to narrow the search down to one table. Interestingly, deleting the table did not have the effect of reducing the file size. The offending table is certainly the largest in the group but it's size does not exceed those of the others by the several orders of magnitude implied by the file size. The only respect in which if differs obviously from the others is that it includes a field with "OLE Object" data type.

  8. #8
    Minty is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    86
    Storing objects / files / pictures in Access is a very quick way to make the file size expand.
    It's recommended that you simply store a path to where the object can be stored on your local network. Much more efficient.

  9. #9
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22
    Quote Originally Posted by gar View Post
    Thanks, I tried that and it did enable me to narrow the search down to one table. Interestingly, deleting the table did not have the effect of reducing the file size. The offending table is certainly the largest in the group but it's size does not exceed those of the others by the several orders of magnitude implied by the file size. The only respect in which if differs obviously from the others is that it includes a field with "OLE Object" data type.
    Right, I imported all the objects except the faulty table into a new database. Then I exported the faulty table from the "old" database to a text file and reimported it to the new database. This appears to have had the desired effect in that most things work properly and the database file is now a more reasonable 5868 KB. - Thankyou for your help but it would be nice to know how the problem came about.

  10. #10
    Minty is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    86
    Once the files approach the 2Gb limit it's not unusual for corruption to set in, as there isn't "space" for Access to do certain things in the background.
    I suspect that was the initial cause of your problem.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,638
    But how was it approaching 2GB? Where there actually objects stored in the OLEObject field? I have never used that field type.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  12. #12
    gar is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2011
    Posts
    22
    I had assumed that the procedure I was using would just put some sort of pointer in the database but it seems that my assumption was wrong. When I right-clicked on the "OLE Object" field and selected "Insert Object", I overlooked the statement in the resulting dialog box that this procedure: "Inserts the contents of the file as an object into your document so that you may activate it using the application which created it.". If all I want is a pointer (e.g. a pathname or URL) what sort of field should I use, short text?

  13. #13
    Minty is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    86
    Yes just use a normal text field.
    You may want to get "Clever" with your storage path, depending on if you are storing multiple objects per parent record?
    For example if your storing a unique ID per parent, then file path for ID 1234 could be \\YourFileServer\MydatabaseFiles\1234\attachment1. pdf

    This way you can create a path automatically , and rather than hard code the top level path store it in a system table, so if you ever need to move your storage you simply change the stored top level path once rather than having to update the whole path.

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

Similar Threads

  1. How do i customize tile sizes on my form?
    By cpzering in forum Forms
    Replies: 6
    Last Post: 03-22-2015, 01:09 PM
  2. Replies: 1
    Last Post: 03-11-2015, 02:38 PM
  3. Replies: 1
    Last Post: 12-04-2012, 10:46 PM
  4. Field Sizes Isues
    By djclntn in forum Queries
    Replies: 4
    Last Post: 12-07-2011, 06:55 PM
  5. Different Screen sizes
    By KevinH in forum Access
    Replies: 0
    Last Post: 07-23-2009, 11:07 AM

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