Results 1 to 9 of 9
  1. #1
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94

    Action queries cause “bloat” of database, how to fix?


    I have an Access database, multi-user, which normally is about 180MB in size. Daily operations by the company users cause this DB to increase on a nominal rate, and so we compact the database periodically. However, I’ve recently added a couple of processes which link data through an ODBC link, and use action queries to place the data in local tables. It replaces the data on each action query. This causes the database to “bloat” in as much as 45MB per each event, and the DB has grown to 450MB in just a few days. A compaction always brings it back down to normal.
    On a google search, I found someone who suggested changing the queries from Access queries to direct VB/SQL code and do ADO closes. So I did that, and testing in a single user test database showed me that it kept the bloat from occurring as much. However, when I put the code out into the multiuser live environment, where there are many other normal select queries or maketable queries occurring on a daily basis, my new SQL query code wasn’t any different than before. The bloat continues to occur.
    What are suggestions on how to stop or prevent this huge increases from repeatedly occurring?

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    if you are making temp tables.....instead consider having the table permanent and instead doing a AppendQuery to put records in and a DeleteQuery to take records out - rather than remaking the table object itself.

    this should definitely help.

  3. #3
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Quote Originally Posted by NTC View Post
    if you are making temp tables.....instead consider having the table permanent and instead doing a AppendQuery to put records in and a DeleteQuery to take records out - rather than remaking the table object itself.

    this should definitely help.
    Nope.

    That's exactly what I was doing... using a delete query to 'empty' the table, then an append query to add new records back in. That's what started the bloat. Change queries to SQL code, still get bloat.

  4. #4
    SteveH2508 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117

  5. #5
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Quote Originally Posted by SteveH2508 View Post
    Wow Steve, it sure looks like from the first page of that article, that it would have an answer for me...

    however...

    you only gave me page 1 of the article... and the answer appears to be continued on pages you didn't give me.

    Help?

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295

  7. #7
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Quote Originally Posted by weekend00 View Post
    the -02.pdf isn't a 2nd page, it's the beginning page of an article on another month. I don't think you can get the whole article unless you give them money.

    update:
    At this point, I've installed code to operate on an activity time frame. If the client/PC has no activity for a set amount of time (controlled by the Administrator), the PC will quit out of Access. Hopefully, there will be times during graveyard and weekends where all PCs will automatically log out, and the database will compact.
    Last edited by KathyL; 11-13-2010 at 02:57 PM.

  8. #8
    JasonMann1979 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    10
    .............

  9. #9
    KathyL is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Did your system have the updates push through on 10/13 and 10/15? We run a very similar multi-user database and after these updates we noticed our queries causing our database to bloat at an alarming rate. Our queries or code hasn't changed at all, yet all the sudden we started seeing the bloat after these updates. We're running Access 2010 with back end for tables and about 25 users. JasonMann1979

    ***************

    We're running Access 2007.

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

Similar Threads

  1. PrintOut action
    By svcghost in forum Programming
    Replies: 11
    Last Post: 10-05-2010, 05:13 PM
  2. command or action " isn't available
    By laqsb in forum Access
    Replies: 3
    Last Post: 07-23-2010, 10:29 AM
  3. No action button
    By dlewicki in forum Forms
    Replies: 6
    Last Post: 12-02-2009, 12:58 PM
  4. Multiple database queries
    By G0zzy in forum Access
    Replies: 4
    Last Post: 08-28-2009, 12:06 PM
  5. Replies: 0
    Last Post: 02-16-2009, 08:21 AM

Tags for this Thread

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