PC Review


Reply
Thread Tools Rate Thread

Ways to keep database size small

 
 
gci1000
Guest
Posts: n/a
 
      21st Oct 2008
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
 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      21st Oct 2008
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,

 
Reply With Quote
 
 
 
 
John W. Vinson
Guest
Posts: n/a
 
      21st Oct 2008
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]
 
Reply With Quote
 
dch3
Guest
Posts: n/a
 
      21st Oct 2008
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]
>

 
Reply With Quote
 
Douglas J. Steele
Guest
Posts: n/a
 
      21st Oct 2008
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:(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]
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DHCP scope is too small for network: possible ways to resolve =?Utf-8?B?U292YQ==?= Microsoft Windows 2000 Networking 7 14th Aug 2006 11:07 PM
Ways to keep my mid-tower computer cooler when gaming? ANTant@zimage.com AMD 64 Bit 29 23rd Apr 2005 12:44 AM
Need advice on best ways to backup data and keep computers 'safe'. OM Windows XP Help 1 9th Mar 2005 12:13 PM
How many ways to maintain an ISP connection (keep alive) -- 11 so far BillR Freeware 3 30th Aug 2004 03:05 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:15 PM.