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 |
|
ColinD
Starting Member
40 Posts |
 Posted - 2010-01-14 : 03:34:28
|
| HiI am using SQL server 2000 as a backend database to an Access 2007 front end application. I use ODBC to link the tables to Access.I have a table with a primary key field called JobID, data type int, with an identity seed set to yes. I was under the impression that the next JobID would be generated as soon as I started typing anything into a field in the new row. My application has worked on this assumption for two years. However it now appears that the new JobID is only being created when you click out of the row. This is causing big problems to my application.I've checked backup copies of my database, and even copies which date back 6 months now have the same problem. Something appears to have changed, not only in the database I'm using, but also all backup copies which were previously working. Have I changed some setting in SQL which I've overlooked? Can anybody help with this?Thanks a lotColin |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 04:01:19
|
| IDENTITY column value is only assigned when the record is inserted, so you can never know the number BEFORE you insert into the table.I can't remember how AutoNumber worked in Access, but I presume from your description that it assigned the number "before" saving the record.Could it be that Access (i.e. your application) was previously providing the number (via some sort of AutoNumber attribute on the column) rather than it relying on a SQL IDENTITY attribute? |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-14 : 04:01:39
|
I think it is a normal behaviour in ACCESS to save not before exiting the row.After that the insert in the table happens and identity columns get there next value only at the time a record is inserted. That is normal behaviour in SQL.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-14 : 04:02:19
|
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2010-01-14 : 04:20:08
|
| Ok thanks folks. Bit of a brain block on my part I'm affraid. It appears that the "on open" event of my application code has NEVER worked, but nobody has noticed, because Access has an annoying little habit of not displaying some error messages in runtime mode. When I run it in design mode, it throws an error. Can't believe that I haven't looked at it in design mode for so long.Thanks againColin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 04:22:04
|
| Hahaha ... stuff like that has NEVER happened to me. Oh No! Not Ever! <thud /> |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-14 : 04:26:22
|
If your SQL table has an identity column then you should be glad that your "on open" event has never worked because the jobid cannot be generated in ACCESS and also in SQL table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Identity seed "Auto number" problem