0

I have an Excel userform with the exact same fields (Textbox's) like in my SQL server (Azure) table, and I want to let multiple users (every user with his Excel file), to add, update and delete records.

The add/insert mode is working perfect, the order of action is like this:

User fill in the textbox's, press "Submit" and an "Insert" sql query sending to the SQL Server table and immediately a Select * SQL query grabs the table and place it into the Excel worksheet.

If user want to Update a record by double clicking the row he/she wants to update a sub procedure event load the record to the textbox's on the userform.

My question starts now, in the update mode.

My goal is that when user entering to edit mode I want to lock the record (by record Id field) in the table in the DB to prevent another user to update the exact record.

So the method should be something like this:

  1. double on the row that the user wants to edit

  2. take the record Id and check in the record is not lock in the DB (in this step I need help)

  3. if the record is not lock, userform opens with all the textbox's filled by the same values like the row in the Excel. (don’t need help in this step)

  4. When the userform opens with the textbox's, we need to take the record Id and lock the record in the DB to prevent another user to update.(in this step I need help)

  5. If the user press submit, update the record and cancel the locking (in this step I need help)

  6. To cover a situation that the user didn’t submit I want to limit the locking in time (if possible) lets say 180 seconds (in this step I need help)

I'm new with SQL, so if you have a better suggestion to my needs, I will be happy to hear it.

Thanks !!

  • Welcome to stackoverflow, too much talk but no code, show some code. – Atul Dwivedi Feb 25 '19 at 13:34
  • This will be a daunting task if you are new to programming or SQL. you're looking for advice on implementing concurrency in user editable screens. Take a look at this article (https://softwareengineering.stackexchange.com/questions/184047/how-do-you-handle-multiple-users-editing-the-same-piece-of-data-in-a-webapp) and also do some searching on concurrency strategies in data editing with multiple users. – Jeremy Feb 25 '19 at 13:53
  • check out https://stackoverflow.com/questions/3564877/how-do-you-manage-concurrent-access-to-forms too – Jeremy Feb 25 '19 at 13:54

0 Answers0