MS Access "ORDER BY" Sorting Algorithm
===

Good people,

I have observed that the MS Access ORDER BY clause sorts records in non-ASCII way. This is different from MySQL - which is generally ASCII-compliant. Let me give you a little background so you understand why this is a problem to me.

Back in 2010, I wrote a generic database transaction logger. The goal was to detect changes occurring on (theoretically) any SQL database and log them in another database. To do this, I use a shadow MySQL database where I maintain a copy of the entire source database. The shadow database is designed using the EAV model so that it is agnostic to the source database schema.

Every once in a while, I read out of both the source and shadow databases, order the records based on their primary keys and format the records to correspond one-to-one. Then, I do a full database compare using a merge algorithm.

This solution has worked okay until last week when a user set it up against an Access database with string primary keys which are not always alphanumeric. All of a sudden the software started logging ghost transactions that have not happened on the source database.

On closer examination, I found out that MS Access orders non-alphanumeric characters in a fashion different from MySQL. As such, my merge algorithm, which assumes similar sort order for both source and shadow records, started to fail.

Now, I have figured out a way I could tweak my software to "cure" such primary keys before using them but it would help a great deal if I know precisely what is the nature of MS Access' ordering scheme. Any ideas will be highly appreciated.

PS: Let me know if there's anything I need to clarify. I am trying to avoid typing too much of what may not be useful.