Prevent Unicode Question Marks in Microsoft Access ADO Updates
Question details
Preserve Unicode text when Microsoft Access updates SQL Server data through ADO.

- Product
- Microsoft Access, ADO, and SQL Server
- Device & OS
- not provided
- Scenario
- An Access application builds an UPDATE statement from recordset values and executes it against SQL Server.
- Observed behavior
- Unicode characters are saved as question marks during the ADO update, although inserts and updates through a bound form preserve them.
Identify the Access code that builds the UPDATE statement and check the SQL Server destination column's data type and length. Confirm that the text still contains the expected characters before the update runs.
Use Unicode parameters for the ADO update
Use a parameterized ADO command to send text without concatenating it into the SQL statement.
In SQL Server Management Studio, inspect the table's column definition. Use a Unicode type such as nvarchar or nchar with sufficient length for the text.
In the Access VBA editor, replace the concatenated UPDATE and cnn.Execute call with an ADODB.Command using the existing SQL Server connection. Set CommandText to an UPDATE with placeholders, such as UPDATE dbo.YourTable SET YourText = ? WHERE YourID = ?.
Use Command.CreateParameter and Parameters.Append to add the text as adVarWChar with adParamInput and an appropriate size. Then append the ID parameter with the matching SQL data type, such as adInteger for an integer ID. Execute the command with Command.Execute.
Run the update with representative Unicode text, then query the row in SQL Server Management Studio. If it still contains question marks, inspect the value before execution and check any conversions or triggers involved in the update.

Update the SQL Server table through a bound Access form
Use a bound form if an ADO command is not required and the existing form already preserves Unicode.
Work with Office documents in WPS Office
WPS Office is a free, lightweight alternative for everyday documents, spreadsheets, and presentations, with a familiar interface and Office format compatibility to make migration easier. It does not replace Access or fix ADO updates to SQL Server.

Frequently Asked Questions
Why does the bound form preserve Unicode when my ADO update does not?
The two update paths can send text differently. A concatenated SQL statement may convert characters before SQL Server stores them; use an ADO Unicode parameter and verify the destination column type.
Which ADO parameter type should I use for SQL Server nvarchar text?
Use adVarWChar with adParamInput and a suitable size when calling Command.CreateParameter. Append parameters in the same order as the placeholders in the UPDATE statement.
Why does a stored procedure still save question marks?
The text may already have been converted before the procedure receives it, or the procedure parameter or destination column may not use a Unicode type. Check the value in Access, the ADO parameter type, and the SQL Server nvarchar or nchar definitions.
Can I recover characters already stored as question marks?
Question marks do not retain the original characters. After correcting the update path, restore or reenter the original text from a source that still contains it.




