Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2009-06-09 : 13:37:59
|
| I have an Access frontend to a SQL database with an Identity column (INT, seed value 1, increment 1) which is also the primary key. I have had many problems inserting records even with a bound form in Access.One other thing that has been confusing me, however, is the fact that the identity column (MachineID) is skipping many numbers, almost as if every time Access fails to insert one, it leaves that next number behind. I have gaps of sometimes a dozen or more unused numbers. Any ideas why? It doesn't matter that much, but I do want to understand it.Duane |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-09 : 13:43:12
|
| that might be because you tried to insert some values from access which caused some constraint/key violations in table. all these constraints are checked only after identity value generation and if it fails, the record along with generated id value is discarded so that next successful inserted record will have a id value which skips the last succeded by 1 due to failed record as it always assumes the next generated id inclusive of failures. |
 |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-09 : 13:50:29
|
| Thank you. Now I have to try to figure out why Acces is not inserting the records even through bound forms. It is only on this one table.Duane |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-09 : 13:52:26
|
| see if there are any constraints/keys defined on table column and check if value inserted by access violates it. also check for presence of any insert triggers on your table and also check any possible logic written in it which prevents inserts from happeining |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-06-09 : 14:52:30
|
| I have had (and I don't know if it will rear its ugly head again) a few column name changes earlier on, and then I got some really bad problems from that. I realize I had to drop some constraints and alter some triggers to get them to work again. For example, one had the wrong data type. This is all from an upsized Access database. I also ended up creating some relationships in the new Access front end while there are some on SQL Server. Is there any general rules of thumb when it comes to making changes after a database is split and upsized onto SQL Server? I know that this is a big question, but it would help me to deal with it later. I have had all sorts of problems.Duane |
|
|
|
|
|
|
SQL Identity column messed up