[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Append Query in Access 2007...

Posted on 2009-02-12
Medium Priority
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

TextReport earned 2000 total points
ID: 23624150
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

Author Closing Comment

ID: 31546085
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

ID: 38871337
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

873 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