Prev: count the nulls in a report calculation
Next: sample database template
From: MJ on 13 Apr 2010 10:31
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: Jeff Boyce on 13 Apr 2010 10:40
JOPO (just one person's opinion)...

Having a "known" structure (i.e., using the Append route) means all your
queries, procedures, reports, forms, relationships, ... can be set up and
left in place.

Using a Make table means starting over (many times), and will mess with your
relationships among tables.

It would help us help you more if we had some context for this decision.
Are you saying that you have something new each time, removing the
possibility of reusing (i.e., Append)? Or is the data actually going to be
used for updating ... that is, do you actually have to wipe out the "old"
version before loading in the new data?

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"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: Jerry Whittle on 13 Apr 2010 10:41
The Make Table query could cause bloat problems if you are doing it
frequently and/or moving over a lot of records.

The Make Table query will not have things like a Primary Key and indexes.
With an Append query, you already have the table set up.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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?
>
> --
>
> MJ
From: Marshall Barton on 13 Apr 2010 11:09
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: James A. Fortune on 13 Apr 2010 11:08
On Apr 13, 10:31 am, MJ <M...(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..
>
> 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

"MJ",

Barring relationships, here's not a huge difference between using a
Make Table query and using an Append Query after a Delete Query. If
you use the Make Table query and have no relationships, you have to
add things like indices afterwards either by hand, through VBA or
through DDL, as well as running the Delete Query first (most cases);
and you're really gonna want those indices if the resulting table is
going to be joined with other tables. If you use an Append Query it
would be good to check that the table you're appending to exists. If
relationships exist, I'd say that swings the advantage to Append
Queries after running Delete Queries. One drawback with that
combination is that you should make sure that the Delete Query
finishes before starting the Append Query (perhaps by using
MyDB.RecordsAffected after a MyDB.Execute strSQL, dbFailOnError). If
the amount of data to be appended is large, you might want to put the
table in a separate .mdb file if you don't need to maintain
relationships, or keep the table in the backend and compact it
occasionally to remove bloat if you do need to maintain
relationships. The query plan shouldn't change much if the amount of
data is anywhere near consistent.

James A. Fortune
MPAPoster(a)FortuneJames.com
 |  Next  |  Last
Pages: 1 2
Prev: count the nulls in a report calculation
Next: sample database template