[AccessD] Access 2007 Append Query - Issue with automatically inserted brackets [ ]

Lambert,

Here is the SQL that was generated by Access.

*** Generated from the Make Table Query 
SELECT Table1.ID, Table1.[Cust Name] INTO [Table2 Result of Make Table]
FROM Table1;


*** Generated from the Append Table Query
INSERT INTO [Table2 Result of Make Table] ( [Cust Name] )
SELECT Table1.[Cust Name]
FROM Table1;



I can make this work be removing the brackets that Access inserts in the
Append Query (Design view).  The strange thing is that the generated SQL
appears to be same no matter if the brackets are in the Design view or
not.  The other REALLY strange thing is that if I go between Design
View, SQL View, and Datasheet View a few times, the brackets disappear
in Design View and it works nicely.  


Thanks for the help.
Brad


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan,
Lambert
Sent: Tuesday, May 18, 2010 2:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2007 Append Query - Issue with
automatically inserted brackets [ ]

Brad,

Spaces in field names *require* brackets.

Please post the SQL for both the Make Table and the Append queries.  

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, May 18, 2010 2:42 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Access 2007 Append Query - Issue with automatically
inserted brackets [ ]

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.