May 7, 2008 at 4:05 pm
Okay, so I am guilty of having a quick Access adp application for my internal users with bound forms. I received a request to make an "audit log" of the transactions made to some of the tables the forms bind to. I made INSERT/UPDATE/DELETE triggers that INSERT the new data to "audit tables" with an identity field. The problem happens when a new record is inserted via the bound forms. Access apparently looks at @@IDENTITY for the auto-number. The problem is that SQL returns the identity from the "audit table" and not the base table. I am over the fact they don't use SCOPE_IDENTITY... no use pulling my hair out, but I still need a quick solution without redesigning the application (management is pretty temperamental about these things).
My solution would be to disable @@IDENTITY while inserting into the audit table, but I have no clue how.
thanks in advance
May 7, 2008 at 4:18 pm
As far as I know, you can't turn it off.
😎
May 7, 2008 at 4:20 pm
Thank you for the quick response! Any suggestions to make Access behave?
May 7, 2008 at 5:09 pm
The best "quick" solution would be for you to take the identity property off of the audit table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2008 at 2:56 pm
kevin mann (5/7/2008)
Thank you for the quick response! Any suggestions to make Access behave?
Keep in mind that you're not "in Access". The objects for the ADP are SQL Server objects, so you're seeing this behavior IN Access (where Access is the UI), but it's SQL Server behavior. you're asking how to make SQL Server to behave.
Why the prohibition from using SCOPE_IDENTITY (which would be your solution in this case)?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 4:58 am
Why the prohibition from using SCOPE_IDENTITY (which would be your solution in this case)?
I don't know... ask the authors of Access. I cannot control which identity is returned back to the UI. Or can I?
May 9, 2008 at 5:59 am
kevin mann (5/9/2008)
I don't know... ask the authors of Access. I cannot control which identity is returned back to the UI. Or can I?
Returned by what?
_____________
Code for TallyGenerator
May 9, 2008 at 9:31 am
I do not know any way to change Access/ODBC behavior in this.
If would still suggest that you take the Identity attribute off the Audit Log table and replace it with a trigger to maintain its own ID field.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 9, 2008 at 10:33 am
kevin mann (5/9/2008)
Why the prohibition from using SCOPE_IDENTITY (which would be your solution in this case)?
I don't know... ask the authors of Access. I cannot control which identity is returned back to the UI. Or can I?
Remember - Access ADP = native SQL objects. You're writing views and stored procedures that are stored in SQL Server, and executed there, so I'm not sure why you wouldn't be able to use anything available in T-SQL.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 4:05 pm
Matt Miller (5/9/2008)
kevin mann (5/9/2008)
Why the prohibition from using SCOPE_IDENTITY (which would be your solution in this case)?
I don't know... ask the authors of Access. I cannot control which identity is returned back to the UI. Or can I?
Remember - Access ADP = native SQL objects. You're writing views and stored procedures that are stored in SQL Server, and executed there, so I'm not sure why you wouldn't be able to use anything available in T-SQL.
It's an automatic feature of Access Linked tables. When the Linked Table is a SQL server table with an identity column, after an Access user adds a new row, Access automatically does a "SELECT @@IDENTITY" to get the new ID value and fills it into the editing grid. What's crazy is that SCOPE_IDENTITY has been the correct way to do this for 8 years now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 9, 2008 at 5:17 pm
rbarryyoung (5/9/2008)
Matt Miller (5/9/2008)
kevin mann (5/9/2008)
Why the prohibition from using SCOPE_IDENTITY (which would be your solution in this case)?
I don't know... ask the authors of Access. I cannot control which identity is returned back to the UI. Or can I?
Remember - Access ADP = native SQL objects. You're writing views and stored procedures that are stored in SQL Server, and executed there, so I'm not sure why you wouldn't be able to use anything available in T-SQL.
It's an automatic feature of Access Linked tables. When the Linked Table is a SQL server table with an identity column, after an Access user adds a new row, Access automatically does a "SELECT @@IDENTITY" to get the new ID value and fills it into the editing grid. What's crazy is that SCOPE_IDENTITY has been the correct way to do this for 8 years now.
yikes - took me doing it to see what you're talking about....yup - that one is a stinkaroo.
unless you unbind the form - you're probably stuck. You could flip the insert order (insert rows into audit table with dummy data to create the rows, THEN insert them into the "real table", THEN update the dummy rows with real values) though a INSTEAD OF trigger, but that's likely to be painful as well.
If you cvan get away with not having that id in the audit table - that would work as well...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 6:23 pm
Matt Miller (5/9/2008)
yikes - took me doing it to see what you're talking about....yup - that one is a stinkaroo.
Yeah, I had to see it for myself too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply