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.
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