👀 Views: 85 💬 Answers: 1 📅 Created: 2025-06-30

ms-access sql-server vba VBA

I've been banging my head against this for hours. I'm confused about I'm stuck on something that should probably be simple. I'm working on a personal project and I'm working with a 'Data Type Mismatch' behavior when trying to update records in a linked SQL Server table from my MS Access 2019 database. The linked table has a column of type `nvarchar`, and I am trying to update this column with values that I know are valid strings, but the update fails. Here’s a simplified version of my VBA code used for the update: ```vba Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb() strSQL = "UPDATE [LinkedTable] SET [TextColumn] = 'New Value' WHERE [ID] = 1" On behavior GoTo ErrorHandler db.Execute strSQL, dbFailOnError Exit Sub ErrorHandler: MsgBox "behavior: " & Err.Description Set db = Nothing ``` I've checked the data types in SQL Server, and they match what I'm trying to write. I’ve also ensured that the linked table shows the correct data type in Access. To troubleshoot, I tried running the same SQL command directly in SQL Server Management Studio, and it works fine, which adds to my confusion. Additionally, I am aware of best practices regarding string lengths and null values, and I am not passing any nulls or excessively long strings. The records being updated are straightforward, and I am not using any calculated fields or complex joins in the update statement. Could there be any issues with the ODBC driver or the way Access interacts with SQL Server? Has anyone faced something similar or can offer insights into why this behavior might be occurring? Any feedback is welcome! This issue appeared after updating to Vba 3.9. I'm working in a Ubuntu 20.04 environment. Any help would be greatly appreciated!