CopyObject or Make Table query

[prasadgov]

Hi,

One of the Access DB I inherited has a code which drops a temp table, runs a make table query, which queries all the fields of a linked table and creates the temp table.
The query uses fields from only the linked table and not other data sources. This is fine and no issues.

Can I rather use the CopyObject to copy the linked table and rename the table to the name of temp table or use the make table query?
DoCmd.CopyObject, "tbltemp_local", acTable, "tbl_Linked"
Which is faster?

TIA

 

[Mark_]

To give the best answer, what is different between the "Temp table" and its source? Why do you have a temporary table if it just replicates data already in your database?

 

[pbaldy]

Probably the most efficient would be emptying and repopulating the local table with an append query. The real question is what purpose does the local table serve?

 

[Pat Hartman]

Probably the most efficient would be emptying and repopulating the local table with an append query. The real question is what purpose does the local table serve?

Both the make table and delete/append queries have the same problem. They cause database bloat.

Rather than make the temp table, why not just use the query that selects the data? If there is a valid reason for importing the data, then the experts use a "side" end where we use a template database so it is always clean and import into a fresh copy of the template. That avoids bloat in the main FE.

 

[Isaac]

Another good way to avoid database bloat is to simply code your shortcut database opener to always copy the network version of the front end to their local and open it each time. That means no matter how much they screw up their front-end version on a daily basis they get a new one anyway.

 

[Pat Hartman]

Another good way to avoid database bloat is to simply code your shortcut database opener to always copy the network version of the front end to their local and open it each time. That means no matter how much they screw up their front-end version on a daily basis they get a new one anyway.

That is what I do but I still don't use make table queries or import temp data in the FE. The nice thing about the "side" end, is the imported/make table data remains after the app closes so it allows for better testing.