View Single Post
  #7  
Old April 13th, 2010, 04:18 PM posted to microsoft.public.access.modulesdaovba,microsoft.public.access.queries,microsoft.public.access.tablesdbdesign,microsoft.public.access
vanderghast
external usenet poster
 
Posts: 593
Default Make Table v. Append to Table: Need Some Expert Design Inputs

And in a split design, the front end part is sometimes a nice place to
create that temporary table, without interference from other users.


Vanderghast, Access MVP


"Marshall Barton" wrote in message
...
MJ wrote:

I am working on updates to a couple related databases (not linked by any
tables) and have a question about virtues of Make Table vs Append queries.

Make Table: I understand that this will create a NEW table where ever it
is
pointed,
so if that table already exists in the destination it
will be deleted, and
the new table written in its place.

Append : In this case I understand that it would require a little
more
"work" to
do same as Make Table, i.e. clearing out table before
appending data.

The 1st major difference, other than additional steps, I can see is if you
have a table structure you wish to retain the Append qry idea might be
more
favorable over the Make table qry.

Okay, now you database gurus...
(1) Are there any other advantages/difierences one over the other?; and
(2) What about database size impacts. grow/shrink, assuming the amount
of
data is fairly consistent from time to time of run?



Creating and deleting a tabledef object is more expensive
(time and space) than just deleting and adding records. The
space used by the deleted records can often be reused, but
the space for the tabledef object is more difficult to
cleanup. Bottom line, make table queries should be avoided
whenever possible.

If you really have to use a temporary (will be deleted and
recreated) table, then it is strongly recommended that you
use a temporary mdb file to contain the table. This may or
may not simplify the code, but it definitely avoids bloating
your real mdb file (and dramatically reduces the need to use
Compact).

--
Marsh
MVP [MS Access]