Prev: count the nulls in a report calculation
Next: sample database template
From: Al Campagna on 13 Apr 2010 11:13
MJ,
Make Table query vs. Append query is like a saw vs. a hammer.
They are two unique tools. So there's really no way to determine
"advantages/disadvantages" between the two. One is great for cutting
boards, and one is great at pounding nails... :-D

Make Table does create a "new" table, but only replaces an exisiting
table if the table name is the same.

Append is meant to add records to an "existing" table. While it might
be used to "act like a Make Table" (ex. two actions - delete all the records
in an exisiting table, and then "append" records to that empty table),
there's really no need to do so when Make Table does the job in one action.

Database size/growth is dependent on many factors.
Indexes, linked Images, record deletions, table deletions, editing,
etc.. etc... all contribute to DB growth.
Regular backing up and Compacting is essential to keep your DB slimmed
down.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"MJ" <MJ(a)discussions.microsoft.com> wrote in message
news:098237E7-8E3F-4A3A-87B1-8FD49A495183(a)microsoft.com...
>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?
>
> --
>
> MJ


From: vanderghast on 13 Apr 2010 11:18
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" <marshbarton(a)wowway.com> wrote in message
news:cg19s5tgb8lch99dveat9s0ubp0vp8p158(a)4ax.com...
> 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]

From: Al Campagna on 13 Apr 2010 11:20
To all,
The points about the realtionships and key fields remaining intact
is a point I did not consider.
That is a definite advantage/diference between Make and Append.
Excellent point! Thanks...
Al

"Al Campagna" <newsgroups(a)comcast.net> wrote in message
news:Otmvcvx2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
> MJ,
> Make Table query vs. Append query is like a saw vs. a hammer.
> They are two unique tools. So there's really no way to determine
> "advantages/disadvantages" between the two. One is great for cutting
> boards, and one is great at pounding nails... :-D
>
> Make Table does create a "new" table, but only replaces an exisiting
> table if the table name is the same.
>
> Append is meant to add records to an "existing" table. While it might
> be used to "act like a Make Table" (ex. two actions - delete all the
> records
> in an exisiting table, and then "append" records to that empty table),
> there's really no need to do so when Make Table does the job in one
> action.
>
> Database size/growth is dependent on many factors.
> Indexes, linked Images, record deletions, table deletions, editing,
> etc.. etc... all contribute to DB growth.
> Regular backing up and Compacting is essential to keep your DB slimmed
> down.
> --
> hth
> Al Campagna
> Microsoft Access MVP 2007-2009
> http://home.comcast.net/~cccsolutions/index.html
>
> "Find a job that you love... and you'll never work a day in your life."
>
>
> "MJ" <MJ(a)discussions.microsoft.com> wrote in message
> news:098237E7-8E3F-4A3A-87B1-8FD49A495183(a)microsoft.com...
>>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?
>>
>> --
>>
>> MJ
>
>


From: John W. Vinson on 13 Apr 2010 14:27
On Tue, 13 Apr 2010 07:31:01 -0700, MJ <MJ(a)discussions.microsoft.com> 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]
First  |  Prev  | 
Pages: 1 2
Prev: count the nulls in a report calculation
Next: sample database template