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?