ADODB multiuser overwriting data

I have database in MS Access 2016, and run update query thru Excel VBA. Sometimes several users can perform this operation at the same time and then overwrite their data when they shouldn’t because of condition in UPDATE statement.

How to block data overwriting?

When I open the table in Access there is a few seconds delay between run query in VBA and the information in the table. Maybe this delay is the problem and it can be eliminated somehow?

Database is divided to FE and BE and connection is persistent.

My query:

UPDATE  (SELECT TOP 1 ID, Col1, Update_time, Update_user  FROM Table1  WHERE Update_user Is Null  ORDER BY Col2 DESC , ID)  AS U_ROW  SET U_ROW.Update_time = Now(), U_ROW.Update_user = [username]; 

In Excel VBA I run it thru ADODB.Command:

With baseRecordsetCommand     .ActiveConnection = objectConnection     .CommandType = adCmdStoredProc     .CommandText = "qryTest"     .NamedParameters = True     .Parameters.Append .CreateParameter("@username", adVarChar, adParamInput, 255, Username)     .Execute recordsAffected End With 
Asked on August 30, 2020 in Sql.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.