Results 1 to 12 of 12
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    Best Practices to avoid temporary storage growth?

    I have frontends that grow in size over time. the largest one right now is 65MB which isn't awful for the amount of time.



    I read that front ends grow in size from executing queries and accumulating temporary data.
    I know you can clear it out using compact and repair but I am wondering if there are good practices/bad practices and things I can seek out to optimize it or even eliminated it?

    Side note: I inherited these front ends and they are all different sizes. Upon compacting and repairing some are still varying sizes but afaik they are identical.
    The backend for all these frontends is an Access DB. In a separate unrelated DB I used passthrough queries and it doesn't seem to increase in size at all so I guess that is one technique? It makes me wonder if the temporary storage grows on the SQL server end though...

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    On the SQL side I can tell you that the way it works is different from Access files.
    SQL server has minimum 2 files: 1 or more data file(s) that contain the data record pages and index pages and one log file that stores all transactions you do. This log file is used to rollback the transactions on your database when necessary. The data file is assigned a certain size when the database is created and is big enough to contain enough empty space to add new records. Once the datafile is full it takes a new extension. The size of this extension can be set with the file properties. The log file is created the same way, with a fixed starting size and a given size for extensions. The cleaning of the log file depends on your recovery settings. When working in full recovery mode, the log is cleaned after each log backup. If you work in simple recovery mode the log is automatically cleaned after each transaction is closed and should never grow to big. So there you can only get into trouble if you add millions of records in one transaction, or if you work with full recovery and never make a log backup.

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    accumulating temporary data
    By temporary data are you referring to Temp Tables? Appending and deleting from temp tables will bloat your database. The work around I use is a temp database with temp tables.
    I just made a demo for another forum but if interested I'll post it here.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi moke, if you're working with a SQL server as backend all temp tables are created in the temp database that is created when you install the server. And I can assure you, this does not bloat the database. It can cause a lot of read/writes though. What can happen is that the temp database will extend to disk memory, which will cause a lot of I/O disk reading and that can slow down the transactions. But it will never bloat your database files.

  5. #5
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The backend for all these frontends is an Access DB.
    In a separate unrelated DB I used passthrough queries and it doesn't seem to increase in size at all
    Not sure which the OP is referring to.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by moke123 View Post
    By temporary data are you referring to Temp Tables? Appending and deleting from temp tables will bloat your database. The work around I use is a temp database with temp tables.
    I just made a demo for another forum but if interested I'll post it here.
    Quote Originally Posted by moke123 View Post
    Not sure which the OP is referring to.
    The passthrough queries work on a sql server. That is a separate project. I only mentioned this because I notice that even after use these frontends do not increase in size.

    As for the main project I am talking about I am not sure if it is actually temp data. I am led to believe it is because I ran one experiment and all the tables are identical sizes between two vastly different sized frontends. (roughly because I used the function listen here)
    The other reason I believe it is temp data from queries is that upon compacting and repairing both frontend1 and frontend2 reduced to about the same size (100kb difference which I think I know what it is)
    Frontend1 was originally 60MB and went down to 5MB upon compact and repair. This one is used daily so it is much larger.
    Frontend2 was originally 7MB and went down to 5MB upon compact and repair.

    I am trying to learn good practices to prevent this kind of thing(and hopefully fix these ones) or perhaps it is inevitable since the backend is an Access DB and not a SQL DB?

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    I had to import data daily from another dept when I worked for a bank.
    That made the BE grow dramatically, and we had to compact regularly.
    Access has no way of reclaiming space even when records are deleted, until then. Foxpro had a purge option, and was a damn sight faster, but still...
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Are there any local tables in the front end? Any import and deleting of data?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Welshgasman View Post
    I had to import data daily from another dept when I worked for a bank.
    That made the BE grow dramatically, and we had to compact regularly.
    Access has no way of reclaiming space even when records are deleted, until then. Foxpro had a purge option, and was a damn sight faster, but still...
    Tch, Dang. I guess going to C# or C++ is the next step if I wanted to get that advanced? (I want to learn C++ anyways and I think I saw something about being able to make forms and stuff so you can probably do DB stuff with it right?)
    I will look into this foxpro!

    Quote Originally Posted by moke123 View Post
    Are there any local tables in the front end? Any import and deleting of data?
    There are some local tables but none that are used anymore. I might get rid of them in an update. They haven't been used since 2009.
    It is all just linked tables and queries. Would getting rid of linked tables and using dao to connect to the backend be better potentially?

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    I would not touch Foxpro. I do not think it is being developed by MS anymore.
    It was initially purchased by MS to kill it, though they took their time about it.

    https://www.google.com/search?q=late...hrome&ie=UTF-8
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I will look into this foxpro!
    don't spend too much time on it

    https://www.foxpro.co.uk/foxpro-end-of-life-and-you/

    if this is still your question
    I read that front ends grow in size from executing queries and accumulating temporary data.
    I know you can clear it out using compact and repair but I am wondering if there are good practices/bad practices and things I can seek out to optimize it or even eliminated it?
    Not having temp tables in the FE is one as already suggested, and another is to use a .accde rather than a .accdb. They will still expand if there are temp tables within the FE but otherwise should not.

    Tch, Dang.
    you seem to be confusing front ends with back ends. You appear to be saying you have a number of apps, all split. All but one uses an access BE and the one uses sql server. The one that uses sql server does not grow in size, whilst all those that use an access BE do.

    If your FE's contain code, you aught to decompile/recompile before compacting. This 'cleans out' the rubbish often generated in code whilst being developed. See this link https://stackoverflow.com/questions/...se-application

  12. #12
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Welshgasman View Post
    I would not touch Foxpro. I do not think it is being developed by MS anymore.
    It was initially purchased by MS to kill it, though they took their time about it.

    https://www.google.com/search?q=late...hrome&ie=UTF-8
    Quote Originally Posted by CJ_London View Post
    Yea haha thanks. I realized it as soon as I googled it. I replied before checking it out.


    Quote Originally Posted by CJ_London View Post
    if this is still your question

    Not having temp tables in the FE is one as already suggested, and another is to use a .accde rather than a .accdb. They will still expand if there are temp tables within the FE but otherwise should not.
    You mean by using dao to retrieve the records rather than using temp tables? or how would I go about not having temp tables??

    Quote Originally Posted by CJ_London View Post
    you seem to be confusing front ends with back ends. You appear to be saying you have a number of apps, all split. All but one uses an access BE and the one uses sql server. The one that uses sql server does not grow in size, whilst all those that use an access BE do.
    I don't believe so, I there is just a misunderstanding/bad explanation. Granted the project the op is actually about is very messily made imo.
    The OP is about Project1 (The inherited one).
    It is made up of a folder called "Project1_User_Files" aka frontends. It consists of several frontends all having a users name appended onto the end of each copy.
    In a separate folder there is a .mdb called "Project1_be" and this is where all the linked tables link to and all the actual data is stored.
    The "User_Files"/Frontends use the linked tables to change data in the backend and sometimes run queries on it.
    There area bunch of queries of all kinds but I believe only a select few are used and most are old. The previous caretaker had a bad habit of not cleaning up after themselves so I am slowly doing so while creating backups as to avoid issues.


    Project2 I developed in Access and it uses a pre-developed SQL server.
    It is a frontend to update data in the SQL server. Someone types in information to find the records they want and then chooses what to update and then in the VBA it generates a passthrough query to send to the SQL server.
    In hindsight it was a mistake to mention Project2 as it just caused confusion in the thread so my apologies.


    Quote Originally Posted by CJ_London View Post
    If your FE's contain code, you aught to decompile/recompile before compacting. This 'cleans out' the rubbish often generated in code whilst being developed. See this link https://stackoverflow.com/questions/...se-application
    I have seen this a few times. It will decrease the size (which its under 5MB so I'm not super worried about decompiling.) but does it also help prevent it from growing over time?

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

Similar Threads

  1. File size growth
    By Jjs2060 in forum Access
    Replies: 3
    Last Post: 01-14-2017, 12:21 PM
  2. Create field with Quarterly growth?
    By jfca283 in forum Access
    Replies: 7
    Last Post: 06-06-2014, 03:04 PM
  3. Growth Calculation?
    By jininho in forum Queries
    Replies: 2
    Last Post: 10-06-2013, 05:40 PM
  4. Query to calculate quarterly growth rate.
    By gromit1 in forum Queries
    Replies: 15
    Last Post: 03-07-2013, 04:00 PM
  5. YTD and MTD Sales growth by activity
    By dudumomo in forum Reports
    Replies: 3
    Last Post: 03-13-2012, 01:42 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