I have moved MS Access 2010 Data to SQL using their tools, and now filtering by Unicode is not working in Access linked tables. I see the linked table column in SQL is "nvarchar" but in Access there is "Unicode compression" set to "No" and I can't change it.
1 Answers
5
votes
It is my understanding that the "Unicode compression" setting only affects native Access (ACE/Jet) tables and has no effect on ODBC linked tables. Instead, what you likely need to do is change the "Collation" setting of the SQL Server database itself by using SQL Server Management Studio:
For example, with the above SQL Server collation setting ("SQL_Latin1_General_CP1_CI_AS") I cannot filter on Greek characters (e.g., 'γιορτή') from Access, but if I change the collation of the SQL Server database to "Greek_CI_AS" then that same Access filter will work.
Edit re: comments
While this solution will work for single-byte code pages that are natively supported by SQL Server (e.g., Greek, which would correspond to Windows-1253), it won't work for languages that lack those code pages and must be represented either by
- a code page that is not supported by SQL Server, or
- Unicode.
ODBC linked tables in Access apparently do not fully support Unicode, passing search strings to SQL Server as 'text', not as N'text', so SQL Server feels compelled to interpret any such text according to the selected single-byte code page (via the "Collation") setting.
