Mdb size growth problem

[Parker]

I have an Access 97 db that has two main forms. The first is a form for
users to build search criteria to query the database. The second form
has a listbox that is populated by what is selected by the user in the
result listbox on the search (first) form. The queries for the
population of both listboxes can be quite large (too large, in fact, to
use a SQL statement to populate th listboxes). So, I have tables that I
insert records into based on what the user selects. Then a "Select * "
SQL statement is used as the rowsource for the listboxes. The tables
are then cleared (with a "Delete * " SQL statement) everytime the user
searches using the first form and when the forms are closed. My problem
is that the mdb file grows substantially everytime the application is
used. The only way I have found to fix it is to compact the database.
Since this is Access 97, I cannot compact on close. Are there any other
options for me? Is there a way to code the listbox population so it
does not cause this file size growth? Thanks in advance for any help.

 

[Douglas J Steele]

One option is to store the temporary table you're using to populate the
database in a separate temporary database.

Tony Toews has instructions for how to do this at
http://www.granite.ab.ca/access/temptables.htm

To get the equivalent of Access's Compact on Close (which, by the way, not
everyone recommends), take a look at Michael Kaplan's TSI SOON (Shut One,
Open New) database add-in at
http://www.trigeminal.com/utility.asp?ItemID=8#8

 

[Parker]

Why is compacting the db everytime you close an issue? Are there
complication that arise if this method is used to solve my problem?
Thanks again.

 

[Albert D.Kallal]

Why is compacting the db everytime you close an issue? Are there
complication that arise if this method is used to solve my problem?
Thanks again.

I don't think it is a "huge" problem, but as a rule, you want to make your
application less maintains free. Further, use of temp data should simply be
avoided as a general design rule. I mean, *if* you have a choice between a
design that uses temp tables, and a design that does not, then you want to
go with the latter. This is especially the case with live data, or data in
the Back end. Less need to compact = less maintenances.

Likely, a good solution is to simply use a form, and pass the form a "where"
clause. (or, simply stuff into the form the SAME sql you now use to create
the temp table). Performance should be better, as you don't have to "copy"
all that redundant data.


I got some screen shoots here of a search screen, I don't need temp tables.

http://www.members.shaw.ca/AlbertKallal/Search/index.html