title: Using SESSION_CONTEXT in SQL from an MS Access VBA frontend
body: <p>I am migrating a MS Access back end database into SQL server.</p>
  
  <p>The existing MS Access front end needs to be be retained.</p>
  
  <p>I am connecting the Access front-end to the SQL database using a service account so that individual users have no direct access to SQL.</p>
  
  <p>I want to record UserId's on record Add and Update actions, but I do not want to have to specify the fields on every call.</p>
  
  <p>I have a hidden table open in Access to maintain a persistent connection to the SQL database.</p>
  
  <p>I created a Session Context object with the UserId in Access using a Sub I call on Access startup, and I have even called the Sub directly before running the record insert.</p>
  
  <pre><code>Sub SqlSetUser()
  Dim qdef As DAO.QueryDef
  
  Set qdef = CurrentDb.CreateQueryDef("")
  qdef.Connect = CurrentDb.TableDefs("dbo_User").Connect
  qdef.SQL = "EXEC sys.sp_set_session_context @key = N'UserId', @value = '" &amp; GetUser() &amp; "';"
  qdef.ReturnsRecords = False  ''avoid 3065 error
  qdef.Execute
  
  End Sub
  </code></pre>
  
  <p>I created a trigger in a SQL table to extract the UserId and add it to the record being added with a similar trigger to handle updates;</p>
  
  <pre><code>CREATE   TRIGGER [dbo].[ReferenceItemAdd]
  on [dbo].[ReferenceItem]
  FOR INSERT
  AS
  BEGIN
      SET NOCOUNT ON;
      declare @UserId as int = try_cast((Select SESSION_CONTEXT(N'UserId')) as int)
      UPDATE ReferenceItem set AddDate = getdate(), AddUserId = @UserId
      from INSERTED i, ReferenceItem a
      where i.ReferenceItemId = a.ReferenceItemId
      SET NOCOUNT OFF;
  END
  </code></pre>
  
  <p>It <strong>only works if I stop the code via a breakpoint and then continue</strong>. If I allow the code to run the record is inserted, and the AddDate is set correctly by the trigger but the UserId comes back with NULL.</p>
  
  <p>How can the UserId be made accessible for a trigger in MS-SQL from an MS-Access front-end?</p>