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,
--
James

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

gci1000 wrote:
> 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,

On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
<(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]

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.

"John W. Vinson" wrote:

> On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
> <(E-Mail Removed)> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"dch3" <(E-Mail Removed)> wrote in message
news:C312D650-BF78-444E-B899-(E-Mail Removed)...
> 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.
>
> "John W. Vinson" wrote:
>
>> On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
>> <(E-Mail Removed)> wrote:
>>
>> >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.
>> --
>>
>> John W. Vinson [MVP]
>>