Results 1 to 5 of 5
  1. #1
    Zakkie is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4

    SQL update trigger, Access reports "another user updated ..."

    I have an Access client on a SQL database (SQL 2017). On some tables there are audittriggers that update auditfields (UpdatedOn, Updatedby).



    When I change a record in MS Access, specifically in tableview or multiline forms, go to another record and then back to the changed record, trying to edit the record gives a warning that the record had been changed by another user.

    Obviously it's SQL server updating the record after MS Access sends the update which triggers the audit. It's no problem if the record is refreshed in any way before trying to edit it the 2nd time.

    I have not build this application, and you can argue that audittriggers like this are not the way to go (what I find mostly is that auditinformation is stored in a seperate tabel). But this "problem" exists for any updatetrigger, as long as it edits the same table (record) that the user edits through MS Access.

    Is there a way around this?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You will need to refresh the records. Access/SQL knows that the underlying records have changed, hence the warning.
    Personally, I would write the changes to another changelog table to avoid the issue, or update the audit fields in Access so that the changes are saved when you leave the record, avoiding the refresh issue.

    I don't think there is anyway around it that I can think of, short of refreshing the underlying recordset.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Zakkie is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4
    Quote Originally Posted by Minty View Post
    You will need to refresh the records. Access/SQL knows that the underlying records have changed, hence the warning.
    Personally, I would write the changes to another changelog table to avoid the issue, or update the audit fields in Access so that the changes are saved when you leave the record, avoiding the refresh issue.

    I don't think there is anyway around it that I can think of, short of refreshing the underlying recordset.
    I know, but it's easy to have the "Created" and "updated" audit info directly available in your forms table/recordset, so I can imagine it's tempting to do it like this

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Understood, in that case see if you can adjust things so that the audit data is updated on the forms rather than the trigger.
    That's no use to you if you are editing things directly in queries or the tables though, in those cases only a trigger can work.

    You could always link the audit table to the records, and display in a sub form or query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I generally advise developers to avoid using triggers. They have a negative effect on database performance and can cause problems in the user interface, as you experienced. Maybe you can try and test to enable tracking in the test environment? https://learn.microsoft.com/en-us/sq...l-server-ver16

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  2. Replies: 1
    Last Post: 08-06-2018, 10:30 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 06-20-2014, 06:09 PM
  5. Replies: 2
    Last Post: 01-03-2014, 09:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums