Trying to learn how to use an Append Query ....

[BobC]

I am trying to understand append queries ...

I was having problems getting an error message:
The INSERT INTO statement contains the following unkown field name:
‘[Part Name]’. Make sure you have typed the name correctly, and try
the operation again.

So I made a copy of the table and tried to append to the copy ... still
get the same error?

Here is the SQL:
INSERT INTO [Copy Of Bobs-UniquePartsListTBL] ( [Part Name], [Model
Number], [Number], [Reference Number], Quanity )
SELECT [Bobs-UniquePartsListTBL].[Part Name],
[Bobs-UniquePartsListTBL].[Model Number],
[Bobs-UniquePartsListTBL].Number, [Bobs-UniquePartsListTBL].[Reference
Number], [Bobs-UniquePartsListTBL].Quanity
FROM [Bobs-UniquePartsListTBL]
ORDER BY [Bobs-UniquePartsListTBL].[Part Name],
[Bobs-UniquePartsListTBL].[Model Number], [Bobs-UniquePartsListTBL].Number;

I'm sure I am doing something stupid? ... but what?

 

[Jeff Boyce]

When you get that error message, chances are there's a typo somewhere. Just
for kicks, what happens if you go back to the tables involved (both the
original and the copy you made) and change the name of the field to
[PartName] (without the square brackets).

Then change your query to refer to [PartName].

What happens?

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[BobC]

Well ... good new and bad news???
After I took the spaces out of all the field names that were in the
files I was able to run the append successfully.
Now the problem is that the original sets of tables have some field
names with spaces? Does this mean I cannot run an append query on these
tables without renaming the fields (and changing them back afterwords)?
If this is true, it will make my effort much more complex!
I am in the beginning stages of wanting to create macros or VB code to
allow someone to append records to existing files in an already existing
program. In the end, I am wanting to provide a means for someone else
to append to linked tables.

When you get that error message, chances are there's a typo somewhere. Just
for kicks, what happens if you go back to the tables involved (both the
original and the copy you made) and change the name of the field to
[PartName] (without the square brackets).

Then change your query to refer to [PartName].

What happens?

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I am trying to understand append queries ...

I was having problems getting an error message:
The INSERT INTO statement contains the following unkown field name: ‘[Part
Name]’. Make sure you have typed the name correctly, and try the
operation again.

So I made a copy of the table and tried to append to the copy ... still
get the same error?

Here is the SQL:
INSERT INTO [Copy Of Bobs-UniquePartsListTBL] ( [Part Name], [Model
Number], [Number], [Reference Number], Quanity )
SELECT [Bobs-UniquePartsListTBL].[Part Name],
[Bobs-UniquePartsListTBL].[Model Number],
[Bobs-UniquePartsListTBL].Number, [Bobs-UniquePartsListTBL].[Reference
Number], [Bobs-UniquePartsListTBL].Quanity
FROM [Bobs-UniquePartsListTBL]
ORDER BY [Bobs-UniquePartsListTBL].[Part Name],
[Bobs-UniquePartsListTBL].[Model Number],
[Bobs-UniquePartsListTBL].Number;

I'm sure I am doing something stupid? ... but what?

 

[John W. Vinson]

Well ... good new and bad news???
After I took the spaces out of all the field names that were in the
files I was able to run the append successfully.
Now the problem is that the original sets of tables have some field
names with spaces? Does this mean I cannot run an append query on these
tables without renaming the fields (and changing them back afterwords)?
If this is true, it will make my effort much more complex!
I am in the beginning stages of wanting to create macros or VB code to
allow someone to append records to existing files in an already existing
program. In the end, I am wanting to provide a means for someone else
to append to linked tables.

Be sure to turn Name Autocorrect (also known as Name Autocorrupt) OFF in your
database! Then delete your Append query. Compact the database after you do so,
and recreate the append query from scratch. The name must match - i.e. if the
name of the field in the table is Part Number then use [Part Number] in the
query, if it's PartNumber use [PartNumber]. It's preferable to avoid blanks in
fieldnames but if you're always careful to enclose the fieldnames in brackets
you can get away with it.

 

[BobC]

No Luck!!!

I deleted the query & compacted and repaired and rebuilt the query 3-4
times in various orders.
There is more than one option ("Track Name Autocorrect Info", "Preform
Name Autocorrect", "Log Name Autocorrect Changes")... I turned them all
off.
I am using Access 2007 and creating the query via a wizard if that makes
any difference?

Here is my latest SQL string:
INSERT INTO [Copy - Bobs-UniquePartsListTBL] ( [Part Name], [Reference
Number], [Number], [Model Number], Quanity )
SELECT [Bobs-UniquePartsListTBL].[Part Name],
[Bobs-UniquePartsListTBL].[Reference Number],
[Bobs-UniquePartsListTBL].Number, [Bobs-UniquePartsListTBL].[Model
Number], [Bobs-UniquePartsListTBL].Quanity

I even rearranged the fields ... just got an error message regarding a
different field with a space in the name.

Any other suggestions???
Thanks!
Bob



FROM [Bobs-UniquePartsListTBL];

Well ... good new and bad news???
After I took the spaces out of all the field names that were in the
files I was able to run the append successfully.
Now the problem is that the original sets of tables have some field
names with spaces? Does this mean I cannot run an append query on these
tables without renaming the fields (and changing them back afterwords)?
If this is true, it will make my effort much more complex!
I am in the beginning stages of wanting to create macros or VB code to
allow someone to append records to existing files in an already existing
program. In the end, I am wanting to provide a means for someone else
to append to linked tables.

Be sure to turn Name Autocorrect (also known as Name Autocorrupt) OFF in your
database! Then delete your Append query. Compact the database after you do so,
and recreate the append query from scratch. The name must match - i.e. if the
name of the field in the table is Part Number then use [Part Number] in the
query, if it's PartNumber use [PartNumber]. It's preferable to avoid blanks in
fieldnames but if you're always careful to enclose the fieldnames in brackets
you can get away with it.