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 also tried 3 ODBC drivers but all produce the same result.

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

 

[tlbroadbent]

Are you familiar with SCOPE_IDENTITY and IDENT_CURRENT? These functions provide alternate ways to obtain the last inserted identity value. SCOPE_IDENTITY returns values inserted only within the current scope. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

You'll probably want to use SCOPE_IDENTITY so that you get the value generated in the SP rather than the value generated in the TRIGGER. See SQL BOL for more details regarding these functions.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[TheBlueDog]

Thank you for a quick response - I did see those functions in BOL when trying to solve this problem. But it's not me asking programatically anywhere for the identity value - it's something SQL just generates and hands back via ODBC to Access, which then has the wrong key for the row it just inserted. I can call the key in my trigger or SP, but that doesn't make it get passed back to Access per se. All I have are linked tables for data entry in a very simple continuous forms entry - when tabbing from one row (inserting the values into table A) on to the next (to enter the next row of data into table A), if the insert trigger creates a row in table B the key value from table B is passed back from SQL thru the ODBC connection to Access, which then changes the previous row in the form to a non-child record whose key is the same value as the key in the (not shown) table B. There's no programming on the interface at all, only just a simple trigger which updates totals in table A and if necessary creates a row in table B.

To be more specific, what I have is a sort of order management tool. My form is a simple Form/subform in Access. The user enters a date and doctor name and hospital, and then enters the subform to list all the products used in a surgery. The 2 parts of the form are linked by a scheduleid. When I enter the products in the child form, I may have one or multiple rows for a single vendor (this is Table A). When a row is entered or updated, my trigger does 2 things. First, it updates a calculated field with an extended amount by multiplying 2 entered fields, quantity and price (in table A). Then the trigger looks at an order table and sees if a summary order exists for that vendor or not. If one already exists, the total of the order itself (Table B) is updated. If the order does not exist, the trigger inserts a row into table B with the amounts from Table A, which can be added to later as more products get entered.

When I enter a row in table A, it gets a PKey generated by identity. Normally, when that is entered, something behind the scenes (as you can see in the trace) hands that PKey back to Access to update the form in which the data was entered so the row then can be displayed as you move on to the next row. Unfortunately, the way SQL (or ODBC, I'm not sure where it happens) chooses to use @@Identity to tell Access the key for the row just entered. If the trigger has to create a new row in table B however, it passes back the key value AFTER it enters the row in Table B and @@Identity has just become the key value from the row entered in table B. Because there are always more rows in Table A than in Table B, Access finds a matching key row in the source table and displays the wrong row in the interface. Usually this means it looks like a surgeon has used a product in surgery which he/she has not. Now there is no real damage done to the data, it's only displayed wrong, and if you move back a record in the parent form and then return to the one just entered, it pulls up the correct records because they are properly tied together by the scheduleid still. But it makes the interface confusing as all get out to see it happen, and of course it happens every time you change product lines for a given surgery because a new row is getting inserted in the orders table.

Does all that make sense? I can get the value I want using functions on the server, but for whatever reason the most basic Access form connected via ODBC to SQL can't get it right. Blah!

 

[tlbroadbent]

Sorry, I don't have any idea how to fix the problem. I suppose there would be fewer problems if you were using an Access Project rather than linking via ODBC. However, some other problems with Access 2000 Projects and SQL Server 2000 have been noted in this forum.

Perhaps, someone with more Access/SQL Server expereince will have an answer.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[TheBlueDog]

Thanks for your efforts - I will look into Access Projects, that's not something I am familiar with and that may hold the answer. The interface is just a temporary one in any case. Again, I do appreciate your willingness to help, and would still appreciate any insight anyone else may have on how the keys get passed.

-KG