[AccessD] Access 2007 Append Query - Issue withautomatically inserted brackets [ ]

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.





More information about the AccessD mailing list