| Author |
Topic |
|
slimjen
Yak Posting Veteran
61 Posts |
 Posted - 2006-12-06 : 08:44:13
|
| I have a database using a sql backend and an access front end. I am trying to add employees to the employee table and I am getting the following error: Violation of Primary Key Contraint 'aaaatblEmployees_PK. Cannot insert duplicate key in object. I am going right to the access front end to add these employees to the table.I have this field autonumbered and it is not duplicating. What can be wrong. Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-06 : 08:45:31
|
| Check all your indexes so there are no other contraints such as lastname or phonenumber...Peter LarssonHelsingborg, Sweden |
 |
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-12-06 : 08:53:28
|
| Make sure that your app (Access) is not trying to insert a number into that field. SQL will automatically add the next value for you according to your seedingRegardsNThe revolution won't be televised! |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 09:16:59
|
| I checked all the other indexes and all are non-unique and non-clustered. I do have four yes/no fields that are under the constraint tab, but they default to 0 in the table. |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 09:19:36
|
| When I try to enter a new employee in access, I go to the end of the last record, enter and it moves to the field after the autonumber field, but it doesn't put a number in it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-06 : 09:28:45
|
| It doesn't until you "save" the records by moving to previous, next or any other line.Peter LarssonHelsingborg, Sweden |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 09:31:30
|
| It's giving me an error before it saves. How do I get around this? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-06 : 09:37:17
|
| Look at index aaaatblEmployees_PK!Which colunm(s) does it cover?Has the identity column in SQL server been reset?Peter LarssonHelsingborg, Sweden |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 09:47:45
|
| I looked at the index properties for aaaatblEmployess_PK. The index key column covers is the IDX_EmpID. The Identity is set to yes, datatype is int doesn't allow nulls. When you say reset, how do I do this? |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 10:25:12
|
| I made a local table of the employee table and I was able to insert records fine. Could it be that in Access, the primary key is an autonumber and it is conflicting in Sql which the identity increment is 1 and the identity seed is set to 1. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-06 : 10:26:45
|
| You should not have two identity fields.The one from SQL should be enough.I think the ACCESS autonumber is reset if comapcting the database.Peter LarssonHelsingborg, Sweden |
|
|
|
slimjen
Yak Posting Veteran
61 Posts |
Posted - 2006-12-06 : 10:35:27
|
| So, should I change the datatype for IDX_EmplID primary key to number and what impact would this have on the data that's already in the table?Thanks so much for helping |
|
|
|
|
insert error