Results 1 to 8 of 8
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    compacting accdb files

    HI i have a .accdb file that is 51mb in size with two tables
    table 1 has 27k records in it

    i have the .accdb file set to compact upon closing.

    When i open and close the file it seems to take longer than it should to compact (defrag) approx 10-13 seconds. this happens even if all i do is open and close the .accdb file (not touching any of the records)



    Does this seem right? can it take this long to compact 27k records that haven't been touched since the last time they were compacted?

    The reason i ask is my backend file (the one in question ) seems to be slowing the performance of the frontend applications attached to it and i think i have it narrowed down to this one particular table but am not sure.

    thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    at 51 M, you have no need to compact.
    I'd just remove it.
    when you get to .5Gig, then Id compact manually.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is your table properly indexes? Usually you would have performance issues with a table that size. How many fields (and specifically memo/long text)? How does it impact your front-end, is it slowing down the loading of forms or reports, running queries or other issues like that?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    hi Vlad, the table has 177 fields in it and 15 of them are memo (long text ) fields.

    how may fields should a table typically have?
    it's slowing down the loading of forms / reports and running queries is slow too.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Does this seem right? can it take this long to compact 27k records that haven't been touched since the last time they were compacted?
    don't think access keeps track of when it last compacted or whether it needs to be compacted - so it has to go through the same process each time - copy record by record to a new db. The other thing compact does is resets queries so a new queryplan needs to be created the first time it is run after a compact. It doesn't take long but may be a factor.

    performance issues are usually related to slow network, poor db design or poor front end design.

    The first is down to your IT dept to make sure the network is fit for purpose.
    The second is down to design of tables/relationships/indexing. A properly normalised, related and indexed db should be fast, even with millions of records. Indications of poor design include use of union queries, need to use domain functions or excessive use of group by queries, lack of indexing on fields regularly used for joining, filter/criteria, sorting. Use of subdatasheets or lookup fields.
    The third is down to designing forms to bring through as little data as necessary. Examples of poor design would be forms/reports based on entire tables or equivalent queries (and don't forget combo/listbox/subforms) or queries based on entire tables to be subsequently filtered by another query.

    Sometimes union queries can't be avoided, sometimes you need to use domain functions, sometimes a table has so few records indexing doesn't really matter etc so there are no hard and fast rules. I'm not saying you should never use them, just as little as possible

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I don't think there is a "magic" number for the number of fields in a table, but 177 is quite a bit, especially with all the memo fields you have as they are usually the ones causing problems:
    https://bytes.com/topic/access/insig...cess-databases
    https://social.msdn.microsoft.com/Fo...orum=accessdev


    Try to split the big table into smaller ones and use one-to-one joins to put it back together when needed. For example if your big table has customer info you could have a main table with the bio demographic data (First name, last name, date of birth, etc.), one table for address, one for company info and so on. In forms you can use the "main" table as record source for the "main" form and add subforms linked by the unique ID to limit the records retrieved from the back-end.

    Also try to limit the forms recordsource to only retrieve the one record you want to look at. I usually have a search combo fed by a saved query with only a few pertinent fields (i.e. Customer Name, Company, etc.) that I do searches on and then use that to limit the forms recordsource to only return that record.

    Cheers,
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are any of the fields repeating data such as Test1, Test2, Test3... where Test is the same feature/attribute? Whenever I see someone post that there's that many fields in a table, I usually suspect lack of normalization, thus a table that's designed like a spreadsheet.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First off, the general opinion among experienced developers is that having Tables with more than 25-30 Fields is almost always a sign that the database is not Normalized. There may be rare cases where this rule is stretched...but 177 Fields is really beyond the pale.

    This many Fields in a single Table almost assures that you're committing 'Database by Spreadsheet.'

    It's really important that you answer Micron's question: "Are any of the fields repeating data such as Test1, Test2, Test3... where Test is the same feature/attribute?"

    Also...as a general rule using 'Compact on Closing' is a bad idea, as Compacting has a bad reputation for corrupting Access databases! At the least you need to be very careful to keep backup copies of your data...at best you should make a backup copy before Compacting.

    And understand that in a multi-user environment, 'Compact on Closing' will only occur when the last user working the database closes the app.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Error on Compacting
    By Paul H in forum Access
    Replies: 10
    Last Post: 08-12-2020, 02:31 PM
  2. Replies: 2
    Last Post: 10-14-2016, 12:27 PM
  3. How to convert from .accdb to executablle files
    By cheesewizz in forum Access
    Replies: 1
    Last Post: 08-29-2012, 06:48 PM
  4. db compacting
    By aiken_Bob in forum Access
    Replies: 5
    Last Post: 06-25-2010, 01:22 AM
  5. Compacting When Closing?
    By Sean04 in forum Access
    Replies: 5
    Last Post: 03-19-2010, 01:16 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