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?

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

---