View Single Post
  #9  
Old April 13th, 2010, 07:27 PM posted to microsoft.public.access.modulesdaovba,microsoft.public.access.queries,microsoft.public.access.tablesdbdesign,microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Make Table v. Append to Table: Need Some Expert Design Inputs

On Tue, 13 Apr 2010 07:31:01 -0700, 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.


Actually, you'll get an error message and it won't make the table.

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?


I very rarely find MakeTable queries either necessary or appropriate. The one
case would be in those (rare but real) cases where a temporary table is
needed; I'll generally use the CreateDatabase() method to create a new backend
database, and make the table in that; I can then delete the .mdb file that was
created when I'm done with it, so as not to cause bloat in my production
database.

Append queries to an existing table have the benefit that the table can have
the desired datatypes, field sizes, relationships, indexes all in place.
Running a DELETE * FROM Scratchpad; query is easier than doing all of that
every time!
--

John W. Vinson [MVP]