Application mdb file grows when application run

[jerryk]

Hi,

My Access application's MDB file grows when the application is run. We do
have a few work tables and create some querydefs, but the remainder of the
data is in an SQL database. And even after running for an hour or so it
grows from 70 mbytes to 300 mbytes. Repairing and compacting the database
shrinks it right back down. Is this normal?

jerry

 

[Guest]

The short answer is yes.

 

[Guest]

The long answer is yes, but your design must be pretty inefficient for that
much growth.

You mentioned work tables which can cause bloat. Avoid work tables if
possible.

Also are you just linking to the SQL Server tables or doing pass-through
queries? The difference is that with pass-through queries the sorting and
grouping is done on the SQL Server. Sorting and grouping take up space which
will cause bloat if done in Access. Also if you aren't using pass-through
queries, the computations are done within Access and all the data must come
over the network to Access. Almost always pass-through queries are faster.
Check out:

http://support.microsoft.com/kb/303968/

If the mdb file stops growing at 300 mb or so, don't even bother to compact
and repair it except for maybe once a month. The size where it stops growing
is how much room it needs to work.

 

[jerryk]

Thanks Dave and Jerry,

While all except 2 or 3 data tables are in SQL Server, there a number of
client side queries the reference the linked SQL tables. I would estimate
about 50-60% of the queries are passthrough.

We are working to move all of the queries, but there will always be some in
Access.

jerry

 

[Pat Hartman\(MVP\)]

That isn't quite true Jerry. Jet makes every effort to pass through EVERY
query. You can turn on the SQL profiler to see the SQL string that Jet
sends to the server to see for yourself that most queries will be processed
in total on the server and only the requested data will be returned to the
client. In the case of non-updatable queries, the SQL strings will be
identical. There will be differences in queries behind bound forms which is
why everyone recommends using where clauses for all queries bound to forms.

Stored procedures will be faster for the same reason that querydefs are
faster than SQL strings created in code. But stored procedures are rarely
needed.