Prev: 90 days and the first of the month
Next: Can't see existing table in Design view for a QBE
From: Brad on 18 May 2010 17:35
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;

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



"Marshall Barton" wrote:

> Brad wrote:
>
> >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.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Brad on 19 May 2010 14:39
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


"Marshall Barton" wrote:

> 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]
> .
>
 | 
Pages: 1
Prev: 90 days and the first of the month
Next: Can't see existing table in Design view for a QBE