If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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 |
#2
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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" wrote in message ... 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 |
#3
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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 |
#4
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
On Apr 13, 10:31*am, 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 "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 |
#5
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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] |
#6
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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" wrote in message ... 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 |
#7
|
|||
|
|||
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] |
#8
|
|||
|
|||
Make Table v. Append to Table: Need Some Expert Design Inputs
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" wrote in message ... 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" wrote in message ... 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 |
#9
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|