Interface getting wrong key back after some trigger-launched inserts

[TheBlueDog]

Desperate for some keen advice here. I have an Access 2000 front end connecting via ODBC to a SQL 2K server, which controls all the logic (I will be transitioning to a new interface, so all logic needs to stay on the server).

I am inserting rows on a multi-row form into table A. Table A is a SQL table which then either inserts a row into Table B or updates an existing row in Table B. An insert/update trigger on Table A manages this process. Unfortunately, when the trigger must cause an insert into Table B, the wrong identity key gets returned to my Access interface, which then displays the "inserted row" from Table A which matches the key value from Table B. I can see in SQL trace the @@identity value getting returned to the interface, and I guess the last key inserted was the one on Table B, so that value is getting passed back. Does anyone know any good way around this, short of abandoning identity keys on my tables (heaven help me then!)? BTW, I used to do this all the time safely in Access 97 with SQL 6.5, but since I have (finally) moved into the new millenium this problem seems to have started somewhere in Access 2000/SQL 2000.

Oh, I have so far also tried 3 ODBC drivers but all produce the same result, all version 2000.xxx.xxx.

Thanks to any hero who can help me resolve this.
-KG

 

[richm2]

Hi KG,

I have a similar problem with an Access 2002 adp project. If you insert into ANY table within an INSERT trigger, Access can't find the new record, and takes you to a blank record. The record is there, Access just can't find it until you requery your table or form, etc. I think Access is using @IDENTITY improperly - when you do a table insert within an INSERT trigger, @IDENTITY holds the value for the most recent insert (the one you performed inside your trigger). I have not found any way to fix this, other than to requery the form etc. for the new record. This is a MAJOR pain that seriously impairs the usability of Access for robust SQL Server apps.

Have you found any work around since your post?

--Rich

 

[TheBlueDog]

Well, I did find a way around it, but not a great one.

After trying multiple ODBC drivers I changed my insert trigger to no longer insert a record into another table with an identity key (it's the second table causing the problem - if you have the second table with fewer rows than the first, it will return the wrong row rather than no row since it finds a value in the first table with a key matching the max identity value from the second table). I made that other table have a calculated primary key, so the correct identity key got returned since only one identity gets created in the process. If you poke around with sql you can see that it finds both identity keys, the first from your primary table, and then the second from the second table you insert a row into via the trigger. ODBC however now only pulls the (max) @@identity after all transactions have been committed. So I concatenated some other fields for a primary key that works and worked around the problem (part of which is the inserted identity key from the first table, assuring the value remains unique), but that's not a great solution for every situation.

In the end even after these changes though I ditched Access as my interface in favor of a Cold Fusion app.