We help IT Professionals succeed at work.
Troubleshooting Question

Need to stop Access front end bloating while running.

pcalabria
pcalabria asked
on
83 Views
Last Modified: 2020-10-28
I have an MS Access o365 front end .accdb file in a split database application.

When running the application the size of the accdb file starts increasing from 231MB until it reaches the 2GB max when it crashes.

I can see the file size increase by viewing the folder with file explorer while the application is running.

All of my tables are linked to the BE... so my code is NOT populating an internal table.  The FE has 161 tables that use 1.21MB of disk space BEFORE and after the size of the accDB increase from 231 MB to 1.8MB (I stopped the application when it reached 1.8MB to export the tables to see if any were increasing in size. )

Please help!
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Pat,
 
 Your saying the FE is growing or the BE?   Sounds like the FE, but if there are truly no tables in the FE, then there is no reason for it to grow.

Jim.   

Author

Commented:
Jim, absolutely.  It is the FE growing.
Did you notice that I exported all the tables to a folder, before and after the bloat?  Read the original post for details.
No new tables and the total size once exported to a folder does not change before or after the bloat.

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try to mark the frontend file as Read-Only, at least as an experiment.
That will reveal if you do some internal writing, while all temp data will directed to true temp files, not kept internally in the frontend.

It will pop a message, that the user cannot change anything in the frontend (which is true) but it will prohibit any writing to and bloating of the frontend.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Are you storing any kind of images?

When does the DB grow?   In general through usage or when you run a specific task(s)?

Jim.

Author

Commented:
Gustav, I'll give that a try now.

Jim, I have a very complicated task that runs, which is broken into three subroutines... I've been trying to figure out which sub is causing the problem, however, I'll try Gustav's suggestion first to see if it gives me an easy solution.  No, I'm not storing any images.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Do you create any temporary tables which are created, used and then deleted?

Have you tried decompiling the application and then recompiling it?
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
An application doesn't grow on its own...unless its alive..  :) ....there must be some kind of operation that stores temporary data that's why your application's FE grows so big.
Just check for Make/Append queries (just search either for action queries or search for the keyword "INSERT" in your code)

Author

Commented:
John, I totally agree... except this application must be alive...

I've found two pieces of code that cause the FE accDB application to bloat...

First Line:- Solved
Set oRSkey = oDB.OpenRecordset("KeywordTemp")
I was able to stop the bloat caused by this line my changing it to:
Set oRSkey = oDB.OpenRecordset("Select * FROM KeywordTemp")

Second Pice of Cde: Not Solved

SELECT * From MyTable WHERE  MyField1 Is Not Null AND MyField2 <>'' AND MyGenericNumber = '" & myPN & '";
Set oRS = oDB.OpenRecordset(strSQLtext)


I have confirmed that these lines were causing the problems.. but I can't figure out why.. at least in the second case.
Its just a SELECT statement.

C&R did not help.  I"m stumped.

       
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Any chance the underlying tables are massive and Access tries to load a a ton of data...how about putting a filter.

Author

Commented:
They are all linked.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
For the 2nd, try changing it to:

SELECT * From [MyTable] WHERE  [MyField1] Is Not Null AND [MyField2] <>'' AND [MyGenericNumber] = '" & myPN & '";
Set oRS = oDB.OpenRecordset(strSQLtext)

as to the first, I'm sure Microsoft would like to see that.

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
and BTW on:

 SELECT *

is only OK if you really need all fields (which is actually pretty rare).  If you don't, don't use it.

Jim.

Author

Commented:
Dale.. yes. the first table is a temporary table.. but I don't delete it.. I delete it's contents. never many records in it so the time to delete is not an issue.

thanks

Author

Commented:
@ Jim.. I gave it a try.. put brackets around object names.. It did not work.
@ John.. yes, the underlying table is massive.. its on the MS SQL Server though as a BE linked table...

Here is what I've tried:
Copped to fresh core
Compact and Repair
Decompile
Compile
Relinked linked tables

Any other ideas what's going on?

Author

Commented:
As Gustav suggested I set the application to READ ONLY and ran it as a test.
Setting the FE to READ ONLY allows this routine to run without errors and without bloating.

Does anyone have any ideas?  Why would the FE bloat to the point that it exceeds the 2GB max size and crashes..
but runs correctly when marked as Read Only.. without displaying any errors???

Thanks Gustav
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Under the hood you are creating temp tables...just show all the hidden/system tables and you should see some pretty big fat tables

Author

Commented:
John, are you referring to the Msys tables?  If so, are you thinking one may not be getting emptied?  
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Probably something like that.

Author

Commented:
This is still a major problem because running long processes is causing the front end to reach the 2GB limit and crash... thereby preventing necessary routines from being run overnight.

My Front End only RECENTLY started increasing in size while executing code.  It returns to the original size after compacting but this is not an acceptable solution.  The bloat occurs when run as both the source code (accDB) and compiled version (accDE).

I suspect John is correct, and that Access may be creating temp tables in the front end... but why all of a sudden would this start happening with an application that has been used every day for more than 15 years?

Here is another example of code that is causing the front end to bloat:

for zz=1 to 10
        strSQLtext = "INSERT INTO TempTableTypo ( MyWord ) SELECT 'ABCDEFGHIJKL" & zz & "' AS Exp1;"
        CurrentDb.Execute strSQLtext, dbFailOnError
next

TempTableTypo is a linked table on a different database which is linked to the source code.

Any ideas would be appreciated.  I worked on this most of the weekend.  Our systems require that we run maintenance over the weekend.  We have done this regularly until this week, when this problem first started. 
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Could you provide a cut-down demo that shows this behaviour?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Any ideas would be appreciated.  I worked on this most of the weekend.  Our systems require that we run maintenance over the weekend.  We have done this regularly until this week, when this problem first started.  >>

 Push the temp tables into a temp DB.    Dale has a very nice article on doing just that with code:

https://www.experts-exchange.com/articles/9753/Creating-and-using-Temporary-Tables-in-Microsoft-Access.html

 The approach I use is similar.   I keep a template in the FE, create the DB, then create the temp tables in the temp DB.  App quits, I kill off the temp DB.

Jim.

Author

Commented:
Jim, I think John was referring to temp tables created by Access itself..

I am not using the FE for temp tables.  my temp tables, which my example shows one, se linked to another database on the local drive.

I delete record in these temp tables... but not the tables themselves... but I do empty them

Author

Commented:
Gustav. .. working on it

Author

Commented:
i did notice some of the MSys tables are fairly large... on of which has 29009 records and is filled with names of objects in my DB
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
"TempTableTypo"

 is not a temp table?

Jim.

Author

Commented:
yes.. but it resides in another database which is linked to the FE
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
There's no reason then for it to be growing.   Microsoft is going to want to see your FE if possible.

Jim.

Author

Commented:
Jim.. and others who are helping..
what do you think of this post

https://www.accessforums.net/showthread.php?t=73273
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
There mixing a few things up in that thread, and I don't see either as causing your problem.

 JET behind the scenes will create temp query defs (start with a tilde (~)) for form and report recordsources, and combo and list box rowsources, and store them for faster execution.  It's faster because the query doesn't have to be costed each time (figure out the best way to execute it).

All Querydefs are costed at first use and the costing plan is stored.  The database may increase in size as a result, but it's nothing like you are describing.

 I think you are tripping over a bug.

Jim.

Author

Commented:
I wrote a short script to illustrate the problem.  The script is below.  In addition, I created an iPhone video which I will attempt to upload.  The FE bloats until it croaks.. ugh... crashes.

Your suggestions will be appreciated. I spent the weekend working on this without resolution.
I"m trying to upload an iPhone video but the app does not seem to have an attach button.

'Populate the linked table TempTableTypos
    For zz = 1 To 1000
        strSQLtext = "INSERT INTO TempTableTypo ( MyKeyword ) SELECT 'ABCDEFGHIJKL" & zz & "' AS Exp1;"
        CurrentDb.Execute strSQLtext, dbFailOnError
    Next
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Just upload it to a free video hosting and share the link... i used streamable.com for a case i wanted to show some footage

Author

Commented:
I posted a simple routine above that shows the bloating.  I'll figure out how to get it from my phone to this forum I will upload it...

I've had nothing but bad luck with Microsoft fixing bugs.  I paid the $500 fee for them to fix the system resource exceeded bug. They opened a case, and the only resolution was that they know that bug exits, and when, or if, enough people complain, they will put it on a list of bugs to fix.

Meanwhile, I can not even reliably compact or compile without first rebooting my machine.  I suspect this bug is related.  I think I have too much code in the module...  

Author

Commented:
hopefully someone Will see some thing in this video that I missed

https://youtu.be/BJl4tleY38U
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Pat,

 Two questions:

 1. On your video, if you repeat the test again, does it increase in size again?

 2. If you change the loop from 1000 to 10000, is the size growth the same or different?

Jim.

Author

Commented:
Yes, it does continue to grow.  The growth seems linear. I ran it first with 100 records and its shows an increase of 1/10 what I get with 1000 records.  

If I continue to run the loop the database will hit the 2GB boundary and crash.  

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
So every insert statement makes it grow?

Jim.

Author

Commented:
yes
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I was able to repo this here.

I'll let you know what Microsoft has to say, but don't expect anything soon.

Jim.
IT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Its not a bug... >>

Don't agree.  Opening the FE read-only avoids it, which proves the operation can be done without making the FE grow.

Inserting data into a table is a fundamental operation and you should be able to do that over and over until you run out of space in the target DB.

Jim.
CERTIFIED EXPERT

Commented:
I think just ensure that the Compact and repair is checked on so that whenever you close the application it automatically repairs it , this way your FE will never grow.

I used to have the problem with Ms Access 2016 after switching on the compact and repair I have now something around 23 MB always.

Regards

Chris 
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Pat,

   So a bunch of us have been tossing this around and the fix for you for now is:


Dim db as DAO.Database

Set db = CurrentDB()

    For zz = 1 To 1000
       strSQLtext = "INSERT INTO TempTableTypo ( MyKeyword ) SELECT 'ABCDEFGHIJKL" & zz & "' AS Exp1"
       db.Execute strSQLtext, dbFailOnError
     Next
 
Set db = nothing

  Which you really should have been doing anyway.  It's always been a bad idea to call CurrentDB() in a loop.  Reason is that each call to CurrentDB() returns a new database object with all the collections refreshed.

  That can make quite a difference in execution time.    In fact, in the past, many used something like this:

Public Function CurDb(Optional bolRefresh As Boolean = False) As DAO.Database


  If objCurDB Is Nothing Or bolRefresh = True Then
     Set objCurDB = CurrentDb()
  End If

  Set CurDb = objCurDB

End Function


 Which uses a global memory variable to hold an instance of CurrentDB() and had a big impact when machines were slower.   Some even used dbEngine(0)(0)  (which is a JET reference to the current DB).   I discuss that here:

https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 But it is the CurrentDB() call that is causing the bloat and Microsoft is looking into it (and by Microsoft, I mean the Access product team).

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<The db object is initiated outside of the loop...so how this is causing the issue....for the loop is a "global" variable as its outside its scope and its available to it.  >>

Not sure I follow what you are saying here.  It's the repeated call of CurrentDB() that causes the problem.

 Using an object variable for the database object prevents the problem.

Jim.

Author

Commented:
Well this was certainly an experience..
Thanks to all the experts... this is certainly one of... (but not the most)... biggest challenges I've faced with Access..

((Second only to the the system resource exceeded... crash while compacting..  and crash while compiling.. which I believe is do to my application being to complex for Access... )

JOHN!  You win my expert of the month award!
The following code completely stopped bloating of the Front End..

Dim oDBlocal as Database
set oDBlocal = OpenDatabase(Path_to_Local_BE)

For zz = 1 To 1000        
strSQLtext = "INSERT INTO TempTableTypo ( MyKeyword ) SELECT 'ABCDEFGHIJKL" & zz & "' AS Exp1"       oDBlocal.Execute strSQLtext, dbFailOnError      

Next   Set oDBlocal = nothing

Note... replacing OpenDatabase(Path to Local BE) with Currentdb cause the bloating to return.

I think this is may be a good opportunity for someone to write about... I'm sure there are others who would benefit.
I've been using CurrentDB for years and have never "noticed" this problem, although Front End Bloating has been a common thing... which I've always though was "normal" for Access..

Thanks again!!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Pat,

 You don’t need to open the backend db,  just use a variable to hold a db reference instead of currentDb (take a look at my last comment).

 While the is nothing wrong with opening the Be directly,  it’s extra overhead and creates a second connection to the backend.

Only time you need to open a BE directly is if you want to use seek on an index.

Jim
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Jim i did missed the db/Currentdb...i reread the answer...i deleted the comment but you are fast...  :)
Although its a bit bizzare how the recurring call of a function could cause such an issue.
If it was a memory issue it would be "natural"...but an in memory process causing disk activity ?? unless it uses some kind of cache and this is left as garbage.