Never use ‘make table’ queries.

[mcgrcoAgain]

I know that this could open a can of worms but is the above statement true. I have always avoided using delet and append due to database bloat. I know its more difficult to set the field types in a make table query but it means that the database doesn’t need to be compacted nearly as much as delet and append.

If my understanding is incorrect please explain top me why delete and append are better.

Thanks

 

[neileg]

Database bloat is easilly corrected with a compact and repair. Depending on what you are doing, a make table query will duplicate data, or result in multiple tables when one would have been sufficient.

Or am I missing something?

 

[GaryPanic]

I have to use make tbles cos whats required is just two complicated to do otherwise , but its deletes tables upon closing d/b down - i have to gather info from numerous tables and this was the logical way of doing it as once i have temp table - I havbe to run a whole set of qry against this end result

it probably could of been coded - but this was the easeist and fastest way of doing it -

 

[boblarson]

And, if you create and delete a table and, as far as bloat goes, it is just as bad as appending to an existing table and deleting, and it could actually be worse as you are adding items which also add to the lifetime limit of objects added.

 

[neileg]

I have to use make tbles cos whats required is just two complicated to do otherwise , but its deletes tables upon closing d/b down - i have to gather info from numerous tables and this was the logical way of doing it as once i have temp table - I havbe to run a whole set of qry against this end result

it probably could of been coded - but this was the easeist and fastest way of doing it -

I would use the same table all the time and just delete the records.

 

[MicroE]

I use make-table, delete, and append queries all the time to create temporary tables. Yes it creates bloat – but I address this issue by running a batch file that copies a fresh fully compiled and compacted db at start-up.

http://www.myaccesstips.com/deploy/deploy.html

 

[neileg]

Something wrong with me, I think. I've never, ever used a make table query....

 

[The_Doc_Man]

To the best of my knowledge, and specifically with respect to the "bloating" factor, there is NO PRACTICAL DIFFERENCE between using delete/append sequences vs. delete table/make table sequences. BOTH will have roughly the same effect in terms of bloat.

If I have doped it out correctly, the delete table/make table sequence causes very slightly more bloat because that also eats the FieldDefs collection associated with the departing TableDefs member - plus the size of that member. For small tables, this is chump change which is why I first said "no practical difference." For complex tables with dozens of fields, this is not so inconsequential.

If the table is indexed, it only gets worse. But again, the same concepts apply either way.

To understand why this is so, just remember that Access garbage collection was designed by the guys at Microsoft. It's a Bill Gates product. It does just barely enough to work, no more. (That has been Bill's design philosophy and way of life for how many years now?) So don't expect miracles. You won't find any.

 

[boblarson]

I did a LONG time ago, but since found that I can just use the query that WOULD have made the table as the table itself, so no table necessary. The only reason I could think of to use a make table and have a temp table is if you are trying to create calculations and store them, which of course is not the good thing to do, as we all know.

But, that being said, it is definitely EASIER for some people to do a make-table than to go the other route as it isn't necessarily easy in complex situations. One other reason for a make-table to do a temp table is to speed up operations when using linked tables to SQL Server/Oracle, etc. where network traffic and large datasets might make it more efficient, but again I would avoid creating a table each time, but append and delete as creating and deleting the table will eventually cause you to reach the Access limits of tables created and then you would have to go import everything into a new database anyway.

 

[MicroE]

A make table query offers better performance than the append and delete method because no validation occurs (checking primary keys, indexes, data types).

A situation where I commonly use make tables is when I pull data via an ODBC connection for reporting purposes. I will dynamically create a pass-trough query to pull data from the server. Then I run a make table query which creates a local access table based on the pass-trough. Then a function is executed to perform the necessary report calculations in the local table and the report is opened (based off the temp table).

Using the append and delete would be slower in this instance.