Append Query in Access 2007...

Posted on 2009-02-12
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
Question by:tomfarrar
    LVL 28

    Accepted Solution

    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
    LVL 7

    Author Closing Comment

    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".

    Expert Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now