How to change SortOrder to avoid the error of "unsupported sorting sort"?

Question

I am working on a program with a .mdb database from a third-party client. Everything was fine until I tried to update the database items. The sortOrder field is invalid. I tried changing it to a shared one with MS Access and no luck. The message I get when I execute the update request:

java.lang.IllegalArgumentException: Given index Index@150ab4ed [ name: (EXART) PrimaryKey number: 2 isPrimaryKey: true isForeignKey: false data: IndexData@3c435123 [ dataNumber: 2 pageNumber: 456 isBackingPrimaryKey: true isUnique: true ignoreNulls: false columns: [ ReadOnlyColumnDescriptor@50fe837a [ column: Column@636e8cc [ name: (EXART) ARCodArt type: 0xa (TEXT) number: 0 length: 30 variableLength: true compressedUnicode: true textSortOrder: SortOrder[3082(0)] ] flags: 1 ] ] initialized: false pageCache: IndexPageCache@3a62c01e [ pages: (uninitialized) ] ] ] is not usable for indexed lookups due to unsupported collating sort order SortOrder[3082(0)] for text index at com.healthmarketscience.jackcess.impl.IndexCursorImpl.createCursor(IndexCursorImpl.java:111) at com.healthmarketscience.jackcess.CursorBuilder.toCursor(CursorBuilder.java:302) at net.ucanaccess.commands.IndexSelector.getCursor(IndexSelector.java:150) at net.ucanaccess.commands.CompositeCommand.persist(CompositeCommand.java:83) at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:268) at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:169) at cultifortgestio.EntradaEixidaDades.Insercio(EntradaEixidaDades.java:76) 

As you can see, Access does not change sortOrder at all, I think it should be 1033, and it remains 3082. Is there a way to change this? As I said, changing Access and running the Compact and Repair database did not help me.

Answer 1

As in other similar situations, the solution was to change the sort order of the affected database. This is usually done

• Opening a database in Access
• changing "New Database Sort Order" (see screenshot below) to "General - Legacy", and then
• Compact and Repair Database operations.

However, the wrinkle in this case was that the Windows locale was set to "Spanish", so the "General" options in Access do not display a value that UCanAccess can update (Jackcess, in fact). The solution for the crawler was to temporarily change the Windows locale to "English ...", follow the steps above to change the sort order of the database, and then change the locale to Windows.

For those who would prefer not to interfere with their Windows language settings, an alternative solution would be to force UCanAccess to create a new empty database file using the newDatabaseVersion option, for example,

 String connStr = "jdbc:ucanaccess://C:/someplace/new.accdb;newDatabaseVersion=V2010"; try (Connection conn = DriverManager.getConnection(connStr)) { } 

open a new database in Access, and then transfer the tables from the old database file to the new one using the import function. The database file created by UCanAccess will have a sort order compatible with update operations.