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. Unfortunately, I've run aground on the dumbest snag I could possibly imagine. 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). This fouls things up because I A) need the correct character shown in the published documents and B) my code is matching the stings returned by the SQL query to values in the spreadsheet and it is failing on these messed up characters.
When I run the query in SSMS, it returns the string with the correct character, and 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, but 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 get the white squares.
Strangely enough, I've had this code run on different computers and gotten different results, but now all my computers get the same result. I can imagine using some hacky hardcoded work around to handle this special case, but I was hoping to see if anyone here had an idea of what might be going wrong so that it could be handled properly and elegantly rather than the hacky approach.
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