Ways to keep database size small

  • Thread starter Thread starter gci1000
  • Start date Start date
G

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,
 
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
 
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,
Click to expand...

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.
 
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.
 
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.
 
Back
Top