RMNOCASE with ODBC and Microsoft Access
I have colleague who wishes to use Microsoft Access to report on an RM database using ODBC. I did so successfully several years ago, but there is a problem between Access and ODBC and RM's RMNOCASE collating sequence. I know that I found a solution way back when, but I no longer remember the solution. And searching this site both with its own internal search engine and using Google Advanced search failed to turn up a solution. Does anybody remember or know how to use Access with RM's database in such a way that columns using RMNOCASE can be accessed successfully?
So I found the solution in these forums, and I had posted it myself. It was just a little bit hard to find.
You can't actually do anything about the RMNOCASE problem while viewing RM's tables from Microsoft Access. But you can make a query that points to RM's tables and uses a Left(fieldname,n) function which points to a column in one of RM's tables. The Left function returns the leftmost n characters, so you can make n be a very large number. The column in the query that's defined by the Left function can be sorted, and the problem is solved. It's a conceptually awkward solution that actually works very nicely in practice.
Actually, much better than the Left function is the Mid function. The Mid function has three arguments - field name, first letter to include, and last letter to include. The third argument is optional, and defaults to the end of the field. So you can do a Mid(fieldname,1) and be sure to pick up the whole field no matter how long it is, and that column of the query can be sorted.
I mostly use Excel to report on my data via the SQLite ODBC connector and because I have SQLiteSpy and the fake NOCASE set up with the RMNOCASE_fake-SQLiteSpy dll (https://sqlitetoolsforrootsmagic.com/RMNOCASE-faking-it-in-SQLiteSpy/)
my SQL queries simply use COLLATE NOCASE without any problem.