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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-26 : 09:23:05
|
| 404 writes "Hi there, I' m currently upsizing an Access database to SQL Server. The problem I'm encountering is in using Autonumbers. In SQL server, there is no straight AutoNumber Data Type, so I have to change it to "int", and add the Identity property. This should work fine but the thing is, the AutoNumber property is updated **ONLY** upon moving to the **NEXT** Record. I'm currently using Microsoft Access for my front end, and Access updates the AutoNumber field **UPON** entering data in **ANY** field. The whole point of this is that the codes I use require the Autonumber field to be updated as soon as any data is changed within the **CURRENT** record, not **AFTER** moving to the next record. So, is there any way I can set SQL Server to behave like this? I use about 50+ tables, Online books says I can use triggers, but it would take too long to create a trigger for each of the 50 tables (Am I correct in this one?). Anyway, thanks for any info you can give me. THANKS." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-26 : 09:26:21
|
| Well, the real problem is that your application isn't written correctly. That's not meant as criticism. You've written it to handle an event structure that exists in Access, not SQL Server. You'll need to re-write it in order to avoid this problem; basically, until the ENTIRE ROW is committed, there is no "auto-number" value because the row has not yet been inserted into SQL Server. I can tell you from personal experience that you will NOT be able to get SQL Server to behave like Access in this regard. |
 |
|
|
|
|
|
|
|
AutoNumber (Identity) Issues in SQL Server