Seriously Numpty Query Question

[TheScullster]

Hi all

We are looking to link an Access database to Great Plains, SQL based
accounting software.
To assist with testing of the link I have been creating tables to provide
discrete sub-sets of data for controlled import to G Plains.

So, the question is, where would a "make-table" query be used in a live
database?
It is a handy tool for the type of excercise I am carrying out i.e.
development and testing, but I wasn't clear on where/if it would be used in
a live application.

TIA

Phil

 

[Klatuu]

Where you can't use it would be anywhere a table with the existing name is
related to any other table.
Personnaly, I avoid using MakeTable queries for a number of reasons.
They use default field sizes that may be incorrect for the data you are
importing.
The field names are based on the field names of the source, but if there is
any grouping or calculations, the field names may not be what you expect.

My preference is to use append queries. The only difference is, you have to
delete the data from the destination table before you append.

 

[John Nurick]

Hi Phil,

Make-table queries in a "production" database? [shudder!] Any database
design that routinely creates new tables as part of normal operations is
probably badly flawed.

The exception is the use of temporary tables, which are sometimes needed
as staging posts when importing or exporting data or to work round other
limitations of the database engine. Even then, a make-table (SELECT
INTO) query is IMHO seldom the best way to proceed, because it doesn't
let you control the field types, indexes, validation rules and so on.
Better to create the table explicitly (e.g. with SQL's CREATE TABLE
syntax, or via a DAO.TableDef object) and then use an append (INSERT
INTO) query..

 

[TheScullster]

Thanks, John and Natuu, my suspicions are confirmed.

Phil