Front end growth everytime DB is opened (1 Viewer)

gojets1721

Registered User.
Local time
Today, 04:54
Joined
Jun 11, 2019
Messages
429
I'm using access as a front end for SQL tables. For some reason, the front end grows in size pretty significantly throughout the course of a day. Every time I open it, it grows by 10%. It keeps doing this every time I exit and get back in, so the file can grow pretty significantly from just opening and closing it.

I did some research and found that this is usually caused by temp tables, however I don't have any temp tables. Mine are all permanent SQL tables.

I've been compacting to address this issue, but I don't necessarily think this is solving the core issue. Any suggestions on what might be causing this bloating if I don't have any temp tables?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:54
Joined
May 21, 2018
Messages
8,463
I might try importing your objects to a clean db. Do you have code in an autoexec macro or running in the opening form? Do you have any local tables?
 

gojets1721

Registered User.
Local time
Today, 04:54
Joined
Jun 11, 2019
Messages
429
I might try importing your objects to a clean db. Do you have code in an autoexec macro or running in the opening form? Do you have any local tables?
Do you have any Make-Table queries?
No make-table queries.

I do however have an autoexec. I didn't design this DB; full disclosure. It appears the autoexec is used to link to the SQL server. Does that make sense? Could this be a contributor to the bloating?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:54
Joined
May 21, 2018
Messages
8,463
The code running in the autoexec could be the cause. If you open and immediately close does it grow in size? Can you post the autoexec code? Relinking should not cause the problem by itself, but there may be more going one when it relinks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:54
Joined
Feb 19, 2013
Messages
16,553
is your app a .accdb or a .accde? .accdb's will be more susceptible to bloat
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,970
The shadow solution to make table queries is permanent tables on which you run delete and append queries. This solution has the identical problem of the make table queries. Access doesn't clean up after itself when you delete data --- and it shouldn't. That would slow down too many processes.

If your problem is as described above - delete/append queries, then let us know and we'll help you to create a "side" BE for just the temp tables to avoid the problem.

Also, if you use a batch file to open your FE, you can download a fresh FE every time the user opens the FE. This also mitigates the bloat caused by more normal things like relinking BE tables as we do with SQL Server.

What version of Access are you using? Older versions were much worse about bloat than newer versions. Probably because they allocate work space from memory without increasing the size of the physical .accdb.
 

gojets1721

Registered User.
Local time
Today, 04:54
Joined
Jun 11, 2019
Messages
429

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 28, 2001
Messages
26,996
If the issue isn't due to the macro, the other possibility is this: When you have a massive query that will create a huge number of large records, Access has to allocate scratchpad memory within itself to "remember" what records will be part of the query. It actually builds a list of the records it has to use. A lot of huge queries that involve a significant percent of the DB could have this effect. Smaller queries have the effect but are usually so small that you don't immediately notice them. The "auto-download" batch file Pat Hartman mentioned would handle this problem perfectly well.

To understand where that list comes from, look at this article.


The first two actions intimately involve the "list" of which I speak.
 

gojets1721

Registered User.
Local time
Today, 04:54
Joined
Jun 11, 2019
Messages
429
Gotcha...also this bloat only happens when multiple users are accessing the DB. Never if a single user is the one opening and closing it. I'm not sure if thats relevant
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,357
Gotcha...also this bloat only happens when multiple users are accessing the DB. Never if a single user is the one opening and closing it. I'm not sure if thats relevant
The correct way to share an Access application is to have each user open their own copy of the Front End file. If you're not doing that, try it and let us know if the problem goes away.
 

Users who are viewing this thread

Top Bottom