Access make table Action Query vs DAO vs SQL

try setting the values for the new fields to Null

newField1:Null  | newField2:Null

or an empty string

newField1:''  | newField2:''

Well, the problem with using null values or empty string is the column's data type.

Instead of running a query that create a table based on datas from another table (or query), you have a couple of options available:
- Use a "CREATE TABLE" query (see here for the syntax: https://www.w3schools.com/sql/sql_create_table.asp)
- With VBA, create a tabledef object, happen fields with data types, then happen the tabledef to the database.

Why would you need to make tables with dummy column names?  When you create a table, you always know all the columns you need at that time.  You may add additional columns in the future if new data becomes available or requirements change.  For example, your application in its original design has no requirement to send emails so you do not create an email field on the Person table.  But after a few months the requirement to email certain reports is added so you then add email address to the  Person table and to the necessary forms, reports, and queries.

The question you asked makes me think you have a spreadsheet rather than a properly normalized relational database.  In a spreadsheet, you are constantly having to add new columns to accommodate new data values.  For example, you start out with 5 employees and you make them columns.  Then you add a sixth and have to change all your formulas to include the sixth.  And then you add a seventh and you have to change all the formulas again.  You would never need to add a column to a properly designed schema because you have a new instance of a data value.

And finally temp tables by their nature create bloat so you should probably not create them except in very special circumstances.  In most cases a Select query will be all you need.  There is rarely a reason to to save data in a separate table when it already exists elsewhere.    Regardless of the technique you use to create temp tables - Make Table, or Delete Rows/Append Rows, Access cannot recover the free space and so has to constantly allocate new memory to accommodate the temp table.  This forces you to compact the database frequently in order to keep the database size manageable.

In Access, you should first add the table to the query 'design mode' using the 'add table' functionality. Next, if you are not performing some sort of processing on the field, you can simply reference the underlying field directly (use the drop-down selector and choose the field name). Lastly, if you want custom fields, you can rename expr1, expr2, etc. to whatever you'd like. Just simply overwrite the expr1 name with a column name you desire. Optionally, you can go into SQL mode and type the complete query directly.

Thanks very much for the help!

If I understand correctly, you are attempting to create a temporary table with some fields from an existing query, plus some additional fields which currently contain no data, but which you expect to populate at some point.  A make table query that looks something like:

SELECT yourTable.Field1, yourTable.Field2, CLng(0) AS TestLong, CDbl(0) AS TestDouble, "" AS TestText
INTO zzTest
FROM yourTable

This obviously will not make the values in TestLong, TestDouble, or TestText as NULLs, but you could resolve that with a simple update query following the make table query.

Thanks Dale!

RE:"however, it prompts at each newly created field..."
I've never seen that happen. Something else is going on. Can you post the SQL you are using ?

Also ... if you execute this SQL:
SELECT Null AS NewField1, Null AS NewField2 INTO tblTest;

Then you get this:


This little 'bug' has been around for a long time.  Binary is not a standard Access Data Type ... but you can create it as shown above.

however, it prompts at each newly created field...

That's probably because you use the DoCmd.RunSQL function.
Instead, you should use the execute function like in the code below:

Dim db As DAO.Database

Set db = CurrentDb
db.execute "YOUR ACTION QUERY HERE", dbFailOnError

Select allOpen in new window

Note: the execute function take an optional parameter, I suggest using dbFailOnError, so an error will be raised in case of problem, and you'll be able to catch it.
More infos here: https://msdn.microsoft.com/fr-fr/library/office/ff197654.aspx

Also

SELECT Null AS NewField1, Null AS NewField2 INTO tblTest;

With this kind of query, as the data type is unknown, Access choose a default one, wich may not be the one you need.
In my opinion, instead of running only one query that create the table and populate it, it is better to run two queries:
One that will create the table structure only, with data types.
One that will populate the table.

CREATE TABLE tblTest(
    newField1 int,
    newField2 varchar(255)
);

INSERT INTO tblTest(newField1, newField2)
SELECT srcField1, srcField2
FROM srcTable;

Select allOpen in new window

Optionally, a third query can be run (before inserting) to create columns constraints, such as primary key, unique index ect ect ....