MS Access with SQL Server back-end update fails without error

Question

This simple VBA statement does not work as expected:

strSQl = "UPDATE Inventory SET NumberOfBlocks = BlocksReserved, LastUser = 'Me' WHERE InventoryID = 1234;"
CurrentDb.Execute strSQl, dbFailOnError + dbSeeChanges

LastUser is updated, but NumberOfBlocks remains unchanged, no error.

If I run this statement in SSMS or as an Access query, it works.

If I use a variable in the VBA statement ..."SET NumberOfBlocks = " & intBlocksReserved & ",..., it works.

Constant works: ..."SET NumberOfBlocks = 555"...

And this one works, too: NumberOfBlocks = (BlocksReserved * 1)

NumberOfBlocks and BlocksReserved are both smallint and not null; the record has a timestamp/rowversion field.

Environment: Access 2016 with SQL 2016 back-end.

Any ideas why my initial statement fails silently? Thanks!

---

More testing confirms my previous findings:

1. Created a new Access db, table Inventory: ID (AutoNumber, PK), NumberOfBlocks (Integer), BlocksReserved (Integer), LastUser (Short Text 10)

2. Created a table in SQL Server:

   [ID] [int] IDENTITY(1,1) NOT NULL, [NumberOfBlocks] [smallint] NULL, [BlocksReserved] [smallint] NULL, [LastUser] nvarchar NULL, [RV] [timestamp] NOT NULL

3. Set ID as Primary Key, linked SQL table, entered test data in both.

4. Run exactly the same code on both tables (only changed the table name):

   Dim strSQl As String

   strSQl = "UPDATE Inventory SET NumberOfBlocks = BlocksReserved, LastUser = 'Me';" CurrentDb.Execute strSQl, dbFailOnError + dbSeeChanges

Result:

Local Access table: NumberOfBlocks = BlocksReserved, LastUser = 'Me'

Linked SQL table: NumberOfBlocks unchanged, LastUser = 'Me'

---

More notes:

• Changing the datatype in SQL Server to int (instead of smallint) did not make a difference.

• However, explicitly converting the field worked:

  ...SET NumberOfBlocks = CInt(BlocksReserved)...

• just as

  ...SET NumberOfBlocks = (BlocksReserved * 1)...

I guess, that turns my post from a question to a heads-up...

Answer 1

Further tests confirmed that this is a bug in the Execute command under the following conditions:

• It's an UPDATE statement where one integer field is assigned to another one, such as SET FieldA = FieldB (same numeric datatype)
• It's a linked table in SQL Server.

The same SQL statement will work fine

• with a table in Access or
• when used in a query.

Tested work-arounds:

• Explicitly convert the field: SET FieldA = CInt(FieldB)... (or CLng...)
• Use any calculation: SET FieldA = FieldB * 1
• Use a variable: SET FieldA = " & intFieldB