How to change SortOrder to avoid "unsupported collating sort order" error?

Question

I've been working on a program with a .mdb database from a third party client. Everything was fine until I've tried to update elements on the database. The sortOrder field is not correct. I've tried to change it to general with MS Access, and had no luck. The message I get when I execute the update query is:

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 the sortOrder at all, I think it should be 1033, and it keeps being 3082. Is there a way to change this? As i said, changing in Access and performing a Compact and Repair database didn't work for me.

I am unable to recreate your issue under Access 2010. After I change the "New database sort order" from "Spanish Modern" to "General - Legacy" and do a Compact and Repair Database the sort order is no longer 3082 and UCanAccess can update the column. Are you sure you are doing the Compact and Repair on the right database file? You could always try dropping the primary key and recreating it to see if that helps any. — Gord Thompson, Aug 11 '15 at 19:05
I can't drop it and recreate it, because another program acces to it in the client environment. These are the steps I make to change the SortOrder: I open de DB in access, I select the table EXART and then change it to general. After that, I do a Compact and Repair Database, I use the Access 2007 version. Could be a problem that the date of creation of the database is 1998? — Amnor, Aug 11 '15 at 19:24
I'm not sure what you mean by "I select the table EXART and then change it to general", but you need to change the sort order for the entire database, not just one table. You need to go into Access Options (screenshot [here](http://ptgmedia.pearsoncmg.com/images/chap3_9780789735973/elementLinks/03fig15.jpg)), change the "New database sort order" to "General - Legacy", and then do a Compact and Repair Database. — Gord Thompson, Aug 11 '15 at 21:00
Same result, the sortOrder keeps in 3082, not sure why, and beggining to get tired of MDB's. — Amnor, Aug 11 '15 at 21:03
Done, with Access language on US english worked, thanks, it was as you said, i don't know what was happening on Spanish variant — Amnor, Aug 11 '15 at 21:19

Gord Thompson · Accepted Answer · 2015-08-12T18:41:35.810

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

opening the database in Access,
changing the "New database sort order" (see screenshot below) to "General - Legacy", and then
performing a Compact and Repair Database operation.

However, the wrinkle in this case was that the Windows locale was set to "Spanish", so the "General" sort options in Access do not map to a value that UCanAccess (Jackcess, actually) can update. The solution for the asker was to temporarily change their Windows locale to "English ...", perform the above steps to change the database sort order, and then change the Windows locale back.

For those who would prefer not to mess with their Windows locale settings, an alternative solution would be to have UCanAccess create a new empty database file via the newDatabaseVersion option, e.g.,

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

open the new database in Access, and then transfer the tables from the old database file into the new one using the Import feature. The database file created by UCanAccess will have a sort order that is compatible with update operations.

Thank you for the newDatabaseVersion option – TiyebM Jan 02 '18 at 16:12 — TiyebM, Jan 02 '18 at 16:12

How to change SortOrder to avoid "unsupported collating sort order" error?

1 Answers1

Linked

Related