Brad Marks
BradM at blackforestltd.com
Tue May 18 15:11:07 CDT 2010
Lambert, To try to get to the bottom of this, I created a new little .accdb with just one table (that has just one field). In my tests, I am not changing the SQL via SQL-View. I am simply using the Access Query Append Table "Design View". There seems to be something funny going on in Design view, because in some cases the insert brackets will disappear as I go between Design View, SQL View, and Datasheet view. I agree that we need the brackets in the resulting SQL, but when the brackets are inserted in Design View, the record insert will not work. Thanks, 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:59 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access 2007 Append Query - Issue withautomatically inserted brackets [ ] Hmm. Did my best to reproduce the problem but could not. I even tried adding the extra spaces in the Insert clause: changing INSERT INTO [Table2 Result of Make Table] ([Cust Name]) To INSERT INTO [Table2 Result of Make Table] ( [Cust Name] ) But no errors came to light. Have your tried to decompile the application? 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 3:34 PM To: Access Developers discussion and problem solving Subject: Re: [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. -- 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.