Access 2007 Append Query – Issue with automatically inserted brack

[Brad]

I ran into something strange while doing a Make Table Query that was followed
by an Append Query (from a “purchased system†table that has many field names
with embedded blanks.)

To understand this issue better, here is what I did to recreate the problem.

Created a new table with only one field named “Cust Name†(note the embedded
space in the field name)

Used an Access “Make Table†query – this worked nicely.

Then I tried an Access “Append Queryâ€. Access generated this field name in
the Append To: field [Cust Name] (Note the brackets that Access
inserted)

When I run it, I receive the following error msg.
“The INSERT INTO statement contains the following unknown field name: ‘[Cust
Name]â€. Make sure you have typed the name correctly, and try the operation
againâ€

I can resolve this by removing the brackets.

This is not a big deal for this little test, but the original production
table has many fields with embedded spaces in the field names. It would be
nice if I could somehow ask Access to not wrapper these field names with
brackets when doing an Append Query.

Is this a feature? Am I missing something obvious?

Thanks,
Brad

 

[KARL DEWEY]

Field names with space must have brackets (use underscore instead).
Post your complete query SQL for both queries.

 

[Brad]

Karl and Marshall,

Thanks for the help – here is more info.

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;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I ran into something strange while doing a Make Table Query that was followed
by an Append Query (from a “purchased system†table that has many field names
with embedded blanks.)

To understand this issue better, here is what I did to recreate the problem.

Created a new table with only one field named “Cust Name†(note the embedded
space in the field name)

Used an Access “Make Table†query – this worked nicely.

Then I tried an Access “Append Queryâ€. Access generated this field name in
the Append To: field [Cust Name] (Note the brackets that Access
inserted)

When I run it, I receive the following error msg.
“The INSERT INTO statement contains the following unknown field name: ‘[Cust
Name]â€. Make sure you have typed the name correctly, and try the operation
againâ€

I can resolve this by removing the brackets.

This is not a big deal for this little test, but the original production
table has many fields with embedded spaces in the field names. It would be
nice if I could somehow ask Access to not wrapper these field names with
brackets when doing an Append Query.

Is this a feature? Am I missing something obvious?

The thing you are missing is that the [ ] are standard name
brackets. They are required if a name includes a non
alphanumeric/underscore character and are optional when the
name starts with a letter and contains only
alpanumeric/underscoare characters. There are many places
where Access inserts them, even when they are not required.
I have never heard of them causing a problem unless the are
usind in a subquery in a FROM clause.

I suspect there is more going on than you have speculated.
To see all the details about a query, you have to switch to
SQL view where you can see the real query instead of the
query designer's user interface.

There may even be something funny in the SELECT INTO query
that is getting in the way of the INSERT INTO query so
double check both queries in SQL view.

 

[Brad]

Marsh,

Thanks for the help.

I agree, it is best to avoid embedded spaces in field names.

Because the fields names in this situartion are being established in another
system, we do not have control over them.


I did some more experimenting with this issue.

If I follow these steps, Access 2007 inserts the brackets and the problem
occurs.

1. Create Query2 with “Query Designâ€
2. Choose Table1
3. Pull the “Customer Name†field into Query2
4. Choose “Append†for this query
5. Choose Table2 for the “Append To Table Nameâ€

If I switch steps 3 and 4, the problem does not happen.

I believe that I learned something new today!

Thanks again for your help.

Brad

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.