Dr. Kipp
Here is a posting by Tony Toews that probaby best addresses the problem you
are experiencing.
HTH
Rob (FMS, Inc.)
From: Tony Toews (
[email protected])
Subject: Re: Database Bloat Prevention Techniques
Newsgroups: comp.databases.ms-access
Date: 2002-02-21 08:28:55 PST
The other post "Database bloat" discusses basically the theory of .mdb
bloating.
I would like to take a little different twist and toss this question
on the table:
What do you consider to be the best ways (tried and true) for
*preventing* the bloat process from happening at all?
(In short, I have an Acc97 app that uses some tables as temporary work
areas.
Each time this app is run, these work tables are flushed and a new set
of data is imported from Excel. The Excel file coming in is about
10,000 rows.
My app grows from 450k to about 4 Meg after just 2 runs. I also use
quite a few DAO recordsets. Does opening and closing a recordset
cause an mdb to gain weight?)
Don't use or create temporary tables in front end. These could be used for
data
importing or speeding up reports. Instead see a Tips page on this topic at
my
website which has sample code for creating an MDB, creating tables within
that MDB,
linking to the tables, unlinking and deleting the MDB.
If you've got dynamic SQL statements, as opposed to stored queries, for
example,
Access is going to need to create temporary storage space each time you run
your SQL.
For example where you are creating queries in strings rather than using
parameter
queries.
KB Article
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q209847
mentions
that "In Microsoft Access, if you use Data Access Objects (DAO) to open a
recordset
and you do not explicitly close the recordset, DAO may hold onto the memory
that it
used for its previous compile until the next recordset is opened. ...
Because the
recordset memory is not released, each time that you loop through code, DAO
may
recompile, using more memory and increasing the size of the database."
The MS KB articles are:
ACC2002: To Help Prevent MDB Bloat, Explicitly Close Recordsets Q289562
http://support.microsoft.com/support/kb/articles/q289/5/62.asp
Acc2002: Running Update Query Causes Database Bloat Q295245
http://support.microsoft.com/support/kb/articles/q295/2/45.asp
ACC2000: Database Bloats When Importing Large Text File Q239527
http://support.microsoft.com/support/kb/articles/q239/5/27.asp
ACC2000: Manipulating Objects with ADO May Cause Database Bloat Q199005
http://support.microsoft.com/support/kb/articles/q199/0/05.asp
ACC2000: Manipulating Objects with DAO May Cause Database Bloat Q197953
http://support.microsoft.com/support/kb/articles/q197/9/53.asp
ACC2000: DB Size Different When DB Is Converted to Access 2000 Q208285
http://support.microsoft.com/support/kb/articles/q208/2/85.asp
ACC2000: Delete Queries Cause Size of Replicated Database to Grow Q207629
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q207629
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
--
Rob
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Need to launch your application in the appropriate version of Access?
http://www.fmsinc.com/Products/startup/index.asp
Need software tools for Access, VB, SQL or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
