MS Access is duplicating updates to a linked table

Question

I have an MS-Access front end connected to an Azure SQL Server database.

I am seeing some very strange updates to the database which I have traced to a merge query being run via an ADODB.Command, the code that runs the merge is below:

Dim cmd As ADODB.Command
   
Set cmd = New ADODB.Command

Dim svr As String
Dim db As String
Dim drv As String
Dim con As ConnectionData
Dim QueryText As String
Dim TransactionID As Integer

TransactionID = Eval("Forms![FrmTransactions]![ID]")

QueryText = "MERGE TblQuantities t" & vbCrLf & _
           "USING (SELECT TblTrnLines.ProductID, TblTrnLines.[Type], Sum(TblTrnLines.Quantity) AS SumOfQuantity" & vbCrLf & _
           "FROM TblTrnLines" & vbCrLf & _
           "WHERE (((TblTrnLines.Deleted) = 0) And ((TblTrnLines.StockTransID) = " & TransactionID & "))" & vbCrLf & _
           "GROUP BY TblTrnLines.ProductID, TblTrnLines.[Type]) s" & vbCrLf & _
           "ON (s.ProductID = t.ProductID) and (s.[type] = t.StockTypeID) and (1 = t.StockLocationID)" & vbCrLf & _
           "WHEN MATCHED" & vbCrLf & _
           "THEN UPDATE set" & vbCrLf & _
           "t.Quantity = (t.Quantity + s.SumOfQuantity)" & vbCrLf & _
           "WHEN Not MATCHED" & vbCrLf & _
           "THEN INSERT (ProductID, StockTypeID, StockLocationID, Quantity)" & vbCrLf & _
           "VALUES (s.ProductID, s.[type], 1, s.SumOfQuantity);"

With cmd
  .ActiveConnection = "[connection_string]"
  .CommandType = adCmdText
  .CommandText = QueryText
  .Execute

The tables TblQuantities and TblTrnLines are both linked tables.

This query is making the updates correctly, however very rarely, the updates seems to get repeated at seemingly random intervals 1 or more times.

Adding a trigger to the table to record updates shows me something like this:

Audit ID   Datetime                    ProductID   AdjustedQty
6305       2023-06-06 16:07:26.330           411            10
6306       2023-06-06 16:07:26.330           185             3
6312       2023-06-06 16:07:26.330             8            10
6313       2023-06-06 16:08:42.390           411            10
6314       2023-06-06 16:08:42.390           185             3
6320       2023-06-06 16:08:42.390             8            10
6321       2023-06-06 16:09:39.940           411            10
6322       2023-06-06 16:09:39.940           185             3
6328       2023-06-06 16:09:39.940             8            10
6329       2023-06-06 16:09:52.387           411            10
6330       2023-06-06 16:09:52.387           185             3
6336       2023-06-06 16:09:52.387             8            10
6337       2023-06-06 16:10:16.280           411            10
6338       2023-06-06 16:10:16.280           185             3
6344       2023-06-06 16:10:16.280             8            10

This seems to happen for about 1% of adjustments, with no pattern to the time between (in this case, 76, 58, 12 then 24 seconds). All the updates are made by the same user, with the same APP in the connection. The gaps in the Audit ID are from me reducing the number of Product IDs for readability. The full data has completely contiguous Audit IDs.

Has anyone seen this before or does anyone have any idea why this is happening and how to prevent it?

Edit: The connection string is ODBC;DRIVER=ODBC Driver 13 for SQL Server;SERVER=[servername].database.windows.net;DATABASE=[dbname];UID=[userid];PWD=[userpassword].

Is the code on azure server in a sql stored procedure ? If so you could add some kind of log code inside the usp to see the name of the app calling the usp, the user / function. If not, i would recommend doing so. Where is this code called ? By pressing a button, or ...? — Mathias Z, Jun 13 '23 at 12:26
if i'm allowed to guess, you probably allow multiple updates from gui so someone clicks on it twice or more — siggemannen, Jun 13 '23 at 13:47
Did you mean to say "sequential Audit IDs" instead of "contiguous Product IDs"? — June7, Jun 13 '23 at 15:09
@siggemannen That was my initial thought, but the timing suggests otherwise. — bendataclear, Jun 13 '23 at 15:53
@June7 No, contiguous and sequential have similar meaning in this context. I prefer contiguous as it better communicates the lack of gaps. — bendataclear, Jun 13 '23 at 15:55
@June7 Yes, sorry, sequential was a red herring, I've edited the question. — bendataclear, Jun 13 '23 at 16:02
Please specify the connection string, or at least the driver/provider part, and some structure essentials (I assume an integer identity PK and no triggers, but it'd be nice to know for sure). Since this is ADO and you're not using the linked table but executing a query on SQL server directly, this is likely unrelated to MS Access. Also, try to remove all red herrings, your query references several columns not seen in your table, and your table contains columns not set/inserted in your query. Try to provide a full [mre] — Erik A, Jun 18 '23 at 05:11
@ErikA The connection string is `ODBC;DRIVER=ODBC Driver 13 for SQL Server;SERVER=[servername].database.windows.net;DATABASE=[dbname];UID=[userid];PWD=[userpassword]`. And it's not been possible for me to replicate the issue, it happens less than 1% of the time. — bendataclear, Jun 18 '23 at 16:30
You didn't say *how* / *when* this function is called. My advice would be: follow the events. -- As Erik wrote, linked tables don't play any role here, since you are running a T-SQL statement on the server. So I think you are looking in the wrong direction. -- Putting the T-SQL into a stored procedure where you can do more logging would be a good idea too. — Andre, Jun 19 '23 at 12:39
@Andre The function is called on a button click, and is part of a larger set of code. Unfortunately this is a codebase I've inherited and it's a real mess, the code block in the button uses some `DSUM` and `DCOUNT` but other than those this is the only ADO code. It's 100% an Access or ODBC issue as the client hangs the entire time the updates are going through. — bendataclear, Jun 19 '23 at 17:26
This is highly relevant info for the question. Add logging to the VBA procedure to see where it hangs (on `cmd.Execute` ?), and if any parts run multiple times. -- It's one more reason to move the T-SQL code into a SP. You could also call it with DAO instead of ADO, maybe it helps. See [here](https://stackoverflow.com/questions/18800727/ms-access-call-sql-server-stored-procedure) — Andre, Jun 19 '23 at 19:50

MS Access is duplicating updates to a linked table

0 Answers0