We help IT Professionals succeed at work.

Append Query in Access 2007...

Medium Priority
1,240 Views
Last Modified: 2013-02-09
Just tried to run an append query in Access 2007 and found a flaw in the process.  Don't know if it was me, or Access 2007.  Here is what I did, and the results:

Added the data fields (to be appended) to the query design.
Changed the query type to "Append".
Selected the Table to Append the new records to.
Then tried to run the query and got an insert error saying the field I was trying to insert to was not valid.  So I looked at the "Append To" row for each data item to see what could be the problem.  When the "append to" name had spaces, the append to name was shown in brackets.  For instance, field "Last Name" was shown as "[Last Name]".  Yet the insert error said "[Last Name]" was an invalid field to insert to.  It was only when I removed the brackets that I was able to append the new records.  I don't remember having that problem before in Access.  Anyone have a good answer?  Thanks.  - Tom
Comment
Watch Question

Yes I would reckon it was a bug, I have just tried it and it automatically put [Last Name] as the append to.

HOWEVER, do not use spaces in field names, follow a naming convention and capitalise significant characters (LastName) but do not use spaces as it causes more work later.

You could raise this with Microsoft on their website but I would just live with it.

Cheers, Andrew

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Tom FarrarConsultant
CERTIFIED EXPERT

Author

Commented:
That works for me.   Just wanted to validate it wasn't something I was doing wrong.  Knew someone here would have the "straight and skinny".

Commented:
If you specify the query type as Append before you select any fields, MS Access 7 will not add the brackets for the fields into which the data is to be stored. Works even if you keep the spaces.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.