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

Answer 2

This is quite interesting. I can reproduce it with Access 2010, SQL Server 2008 R2, ODBC Driver 17 for SQL Server.

But only if the (N)VARCHAR column is included in the UPDATE query!
UPDATE AAA SET Smallint2 = Smallint1, Int2 = Int1; works.

CREATE TABLE AAA (
    ID int IDENTITY(1,1) NOT NULL, 
    Smallint1 SMALLINT NULL, 
    Smallint2 SMALLINT NULL, 
    Int1 INT NULL,
    Int2 INT NULL,
    foo NVARCHAR(255) NULL,
    RV TIMESTAMP NOT NULL,

    CONSTRAINT PK_AAA PRIMARY KEY (ID)
)
GO

INSERT AAA (Smallint1, Smallint2, Int1, Int2, foo) 
VALUES (1, 0, 77, 9999, 'asdf'), 
       (3456, NULL, NULL, 1234, 'null')

Access-VBA:

Sub TestAAA()

    Dim strSql As String

    strSql = "UPDATE AAA SET Smallint2 = Smallint1, Int2 = Int1;"
    CurrentDb.Execute strSql, dbFailOnError + dbSeeChanges

    Stop
    ' Requery table => UPDATE was successful!

    ' Edit and save values in Smallint1 / Int1

    strSql = "UPDATE AAA SET Smallint2 = Smallint1, Int2 = Int1, foo = 'with NVARCHAR';"
    CurrentDb.Execute strSql, dbFailOnError + dbSeeChanges

    Stop
    ' Requery => Smallint2 / Int2 are not updated, "foo" is!

    strSql = "UPDATE AAA SET Smallint2 = CInt(Smallint1), Int2 = CLng(Int1), foo = 'with Conversion';"
    CurrentDb.Execute strSql, dbFailOnError + dbSeeChanges

    ' Requery => Smallint2 / Int2 are updated!

End Sub

Results:

• initial state
• after first update
• after manual edit and second update
• after third update

+----+-----------+-----------+-----------+-----------+-----------------+
| ID | Smallint1 | Smallint2 |   Int1    |   Int2    |       foo       |
+----+-----------+-----------+-----------+-----------+-----------------+
| 1  | 1         | 0         | 77        | 9999      | asdf            |
| 2  | 3456      |           |           | 1234      | null            |
|    |           |           |           |           |                 |
| ID | Smallint1 | Smallint2 | Int1      | Int2      | foo             |
| 1  | 1         | 1         | 77        | 77        | asdf            |
| 2  | 3456      | 3456      |           |           | null            |
|    |           |           |           |           |                 |
| ID | Smallint1 | Smallint2 | Int1      | Int2      | foo             |
| 1  | 222       | 1         | 988888888 | 77        | with NVARCHAR   |
| 2  | 333       | 3456      | 999999999 |           | with NVARCHAR   |
|    |           |           |           |           |                 |
| ID | Smallint1 | Smallint2 | Int1      | Int2      | foo             |
| 1  | 222       | 222       | 988888888 | 988888888 | with Conversion |
| 2  | 333       | 333       | 999999999 | 999999999 | with Conversion |
+----+-----------+-----------+-----------+-----------+-----------------+