Ways to keep database size small

[gci1000]

Instead of constantly having to compact database, what is one way to use
tables, or temp tables differently in such a case that the access database
will not grow up so far. Right now, I have "delete * from table", then later
.... "insert into table from ...". Any suggestions ?
Tanks,

 

[John Spencer]

Use a temporary Dateabase and populate the tables there.

See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example

I use a slight modification of Tony's methodology, where I build the table
design in my database with no records in the table. I then name the table
with an _SRC appended to the end. Then I copy the table over into the
temporary database and drop the appended _SRC when I do so. Then I link to the
table in the temp database.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Instead of constantly having to compact database, what is one way to use
tables, or temp tables differently in such a case that the access database
will not grow up so far. Right now, I have "delete * from table", then later
... "insert into table from ...". Any suggestions ?
Tanks,

If you really need to routinely empty and refill a table (hint: you probably
DON'T, since a SELECT query may get you the same result), consider putting the
temp table into a temporary .mdb file, created as needed, destroyed when
finished. Tony Toews has an example at
http://www.granite.ab.ca/access/temptables.htm

Again; note that if you're creating a table just so that you can base a report
or an export on it, you're wasting time and effort; you can base a report or
an export on a Query without creating a table.

 

[dch3]

Going with this method, you can write a VBScript that automatically deletes
the temporary table, replaces it and then starts Access. Its then just a
matter of teaching the users to use the script to start the database -
they'll be none the wiser. We do something similar to ensure that all users
have a current copy of the front end on their local machines.

 

[Douglas J. Steele]

You don't really need VBScript.

As long as no connections are open to the temporary database, you can delete
it from the front end using the Kill statement.