Append Query: SQL to add a new record?

[Faraz Azhar]

Hello

Im trying to use the append query to add a new record to a table.

I have a table which has four fields (ID, Name, Email, Age). The ID
field is autonumber.

I write the following SQL statement:

INSERT INTO Table1 (Name, Email, Age)
VALUES ("Faraz", "(e-mail address removed)",24);

The above sql doesnt work. It gives error. I think its because I am
not providing the autonumber, but I cant provide the autonumber.... I
want to program in such a way that things arent hard-coded. So
autonumber is unknown to me, Access should assign it on its own. So
what else could be the error?

Any help please.

 

[John W. Vinson]

Hello

Im trying to use the append query to add a new record to a table.

I have a table which has four fields (ID, Name, Email, Age). The ID
field is autonumber.

I write the following SQL statement:

INSERT INTO Table1 (Name, Email, Age)
VALUES ("Faraz", "(e-mail address removed)",24);

The above sql doesnt work. It gives error. I think its because I am
not providing the autonumber, but I cant provide the autonumber.... I
want to program in such a way that things arent hard-coded. So
autonumber is unknown to me, Access should assign it on its own. So
what else could be the error?

Any help please.

Ummmm... care to share WHAT error? We can't see it from here, and I see no
reason your query shouldn't work.

Do note that if you store an Age in a table, you can be absolutely sure that
it will be WRONG a year from now, and that Name is a reserved word and a bad
choice for a fieldname. That indeed might be the problem.

 

[John Spencer]

Try putting square brackets around the field name "Name". Name is a
reserved word in Access and sometimes using reserved words as field
names will cause an error.

INSERT INTO Table1 ([Name], Email, Age)
VALUES ("Faraz", "(e-mail address removed)",24);

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Faraz Azhar]

Ummmm... care to share WHAT error? We can't see it from here, and I see no
reason your query shouldn't work.

Do note that if you store an Age in a table, you can be absolutely sure that
it will be WRONG a year from now, and that Name is a reserved word and a bad
choice for a fieldname. That indeed might be the problem.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Yes I am aware of Age. But thats not the problem. When I type the
query in SQL view, and then click Execute, it says "Access cant append
all the records in the append query." then it gives a pretty long
explanation in which it states it wasnt able to add 1 record to the
table due to key violations.

Please note my table has four fields. The first one is ID which is an
autonumber. I dont specify that field in the append query. I think
that is the problem. But how do I specify an autonumber in append
query?

 

[John W. Vinson]

Yes I am aware of Age. But thats not the problem. When I type the
query in SQL view, and then click Execute, it says "Access cant append
all the records in the append query." then it gives a pretty long
explanation in which it states it wasnt able to add 1 record to the
table due to key violations.

Please note my table has four fields. The first one is ID which is an
autonumber. I dont specify that field in the append query. I think
that is the problem. But how do I specify an autonumber in append
query?

Ordinarily you should just leave the autonumber out of the append query; new
autonumber values will be assigned to the new records. You can include a Long
Integer field in the source of the append if you need to actually assign a
value to an Autonumber field - this is useful if you need to start the
autonumbers at a particular value, but otherwise isn't much use!

I strongly suspect that there is some OTHER field that is causing the Key
error. Do you have any unique indexes or relationships on any of the other
fields?

Alternatively you might be running into a longstanding bug in the assignment
of new autonumbers. Allen Browne has a good page discussing the variations on
this problem:
http://allenbrowne.com/ser-40.html

 

[Faraz Azhar]

Ordinarily you should just leave the autonumber out of the append query; new
autonumber values will be assigned to the new records. You can include a Long
Integer field in the source of the append if you need to actually assign a
value to an Autonumber field - this is useful if you need to start the
autonumbers at a particular value, but otherwise isn't much use!

I strongly suspect that there is some OTHER field that is causing the Key
error. Do you have any unique indexes or relationships on any of the other
fields?

Alternatively you might be running into a longstanding bug in the assignment
of new autonumbers. Allen Browne has a good page discussing the variations on
this problem:http://allenbrowne.com/ser-40.html

Yes there is a relationship of this table with another. There is
another table called Table2 which has 3 fields (ID, Code, Description)

The ID field is linked with the Table1 which we are trying to append.
Therefore it is a Long integer and a primary key. Then the Code field
is also a long integer and primary key. (There are two primary keys in
this table) The third field is description. The reason for two primary
keys is that there is another table; Table3; which has similar
relationship with Table2. The Code field is linked between Table2 and
Table3.

Anyway If I am manually adding records to Table1 (in which Im running
Append query) It doesnt give any requirments that Table2 should also
be updated, so therefore I think relationship might not be causing
error. What do you think?

 

[Faraz Azhar]

I've discovered the problem. My Age field was mistakely set to Indexed
- Yes (No Duplicates). So there were two people with same age so it
was causing error. I've resolved the error now.

Thank you all for your replies and support !!

 

[John W. Vinson]

I've discovered the problem. My Age field was mistakely set to Indexed
- Yes (No Duplicates). So there were two people with same age so it
was causing error. I've resolved the error now.

Thank you all for your replies and support !!

Thanks for posting back, Faraz, and I'm glad you found the problem - I was
certainly getting puzzled!