Brad wrote:
I can recreate the problem with these steps (takes about 2 minutes, if you
want to experiment)

Create new Access 2007 database
Create Table1 with one field called “Customer Name”
Add one record to this table

Create Query1 with “Query Design”
Pull the “Customer Name” field into Query1
Choose “Make Table” query (New table name = Table2)
Run this (From the Access Design panel – Big Red Exclamation Point)
This creates Table2 nicely – so far, so good

Create Query2 with “Query Design”
Choose Table1
Pull the “Customer Name” field into Query2
Choose “Append” for this query
Choose Table2 for the “Append To Table Name”

Access now puts [Customer Name] in the Append To: field

I then push Run and get this message
“The INSERT INTO statement contains the following unknown field name :
‘[Customer Name]’. Make sure you have typed the name correctly, and try the
operation again.”

If I manually remove the brackets in the “Append To: field, the insert will
work.

I am not changing any SQL via the SQL-View.

I can get around this issue, but I am curious why this is happening.

Thanks
Brad

PS. Here is the underlying SQL that Access 2007 has generated

*** Query1 (Make Table)
SELECT Table1.[Customer Name] INTO Table2
FROM Table1;

*** Query2 (Append)
INSERT INTO Table2 ( [Customer Name] )
SELECT Table1.[Customer Name]
FROM Table1;


My Access 2007 machine was wiped for other uses so I can't
try it there. I'll have to take your word for what happens
there.

I guess this is a place where Access 2007 adds the [ ] when
it's not appropriate. In A2003 when I tried this in the
query designer, Access did not add the [ ] and if I added
them myself, Access removed them. Access 2010 did not add
the [ ] either. Regardless, I ended up with the same SQL
view you have and it ran fine.

This is another good reason to never use names that require
[ ]. I don't do that or I worked in SQL view where the [ ]
are under my control so I've never seen this particular
problem.

I think your best action at this point is to rename the
field without the space. If you do that, I believe you will
be a lot happier in the future.

--
Marsh
MVP [MS Access]