Access front end MSSQL Backend and a trigger inserting on another table.
Hi All,
I'm trying to solve an issue.
The basic situation is like this:
There's an Access front-end and it inserts a record on a MSSql Back-end database with a Integer as identity. But the table on the back-end needs to have a trigger to send the data to another intermediate database. (a Gateway database) a thrid party reads this database using en window service to update their database.
The issue is this:
The trigger inserts a record in the gateway database using a GUID as identity after the record is inserted in the back-end database. By Access
Access retrieves the Identity of the last insert with @@identity and recives the |Guid from the other database. Instead of the integer off the first insert.
Access should use Scope_identity() to retrieve the correct (integer)Id
Now the question:
Is there a way to make Access use Scope_identity instead of @@identity? or another workaround NOT using MAX(Id), as it's a multi user enviroment?
Re: Access front end MSSQL Backend and a trigger inserting on another table.
And you have to live with the identity PK in access? You cannot switch to a GUID PK or add a GUID as an alternate KEY, so that you can produce a guid upfront?
In an accounting system that I have we maintain the next batch and transaction numbers for a fiscal year in a control table and get the next ID using this logic in a SPROC. I just checked on of my client sites and one of these tables has 374,000 rows and never got a problem using logic like the below.
Nice thing about this logic is no transaction/lock is required - it's multi-user SQL safe from the get go.
Code:
RedoB:
Set @DataValue=(select DataValue from Control_T where FiscalYr=@FiscalYr and Item='LAST BATCH')
Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
Update Control_T Set DataValue=@NewDataValue,Tdate=@Tdate
Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
If @@RowCount<>1 Goto RedoB