I have an Excel VBA project that iterates through a list in a parameter table and populates data using the rows of the parameter table to provide arguments for the parameters of the SQL Query to a SQL Server DB. This is to produce n versions of an Excel document from n rows in the parameter table in the master macro-enabled workbook.
Some of the data requested in the SQL DB includes the é (e-acute) character but when it runs through the ADODB recordset object, it comes out the other side as a □ (WHITE SQUARE, U+25A1).
A) I need the correct character shown in the published documents.
B) My code is matching the strings returned by the SQL query to values in the spreadsheet and it is failing on these characters.
When I run the query in SSMS, it returns the string with the correct character.
When I use the Excel Query to connect and query the DB manually, it shows the correct characters in the query table and in the cell.
I get the white squares when I use VBA to fetch the data into and ADODB recordset and then copy that data to a list object table with:
.Range(<<Address>>).CopyFromRecordset
I've run this code on different computers with different results, but now all my computers get the same result.
I can imagine using some hardcoded work around to handle this special case, but I was hoping it could be handled properly.
The substituting of foreign characters (accents, umlauts, Japanese, Arabic, Russian, etc) to □'s and ?'s occurs due to not using utf8.
Two places to look, first a quick check of your connection string, does it use unicode an utf8 charset, eg:
ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=your_server;Database=your_database;User=your_username;Password=your_password;Charset=utf8;
The other place it could be happening is VARCHAR fields, change them to NVARCHAR to support utf8.
Alternatively in SQL Server you can set the collation on a varchar field, like this:
Utf8Column VARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC_UTF8