Access add-in with "Selected Collating Sequence Not Supported" error message

[Boris]

I created an Access 2003 add-in (.NET add-in) which opens a connection to
the current database using ADO (or ADO.NET as it is called in the .NET
world). The Access add-in receives some data from outside the database and
then adds this data to the tables in the current database. From time to time
adding fails and I get the error message "Selected Collating Sequence Not
Supported" (error 3228). Then I manually open a table, close it and start
the add-in again - and everything works.

Of course I'd like to know why sometimes adding fails at all and why I get
such a strange error message. I read the knowledge base articles about
different language editions of Access and New Database Sort Order. But as
the database was created on my own computer, as I never changed any language
setting or the New Database Sort Order setting the problem must be somewhere
else.

In order to debug my add-in: Is it possible somehow to observe a connection,
like writing all SQL statements and the replies into a log file? Is there
any switch for this in Access? Or is there any configuration setting I could
change in the .NET world? I appreciate any hints how to track this error.

Boris

 

[Mary Chipman]

Unfortunately Access doesn't have much in the way of logging or
profiler tools like SQL Server does. I'd suggest debugging the code in
the VS IDE to see if you can repro the problem when stepping through
the code. How exactly is Access "receiving" the data?

--Mary

 

[Boris]

Unfortunately Access doesn't have much in the way of logging or
profiler tools like SQL Server does. I'd suggest debugging the code in
the VS IDE to see if you can repro the problem when stepping through
the code. How exactly is Access "receiving" the data?

I use ADO.NET's OleDbDataAdapter and DataTable to load an Access table,
delete, update or add some rows and call Update() to store the changed data
in the Access table again. This is when sometimes an exception is thrown and
the connection to Access must be closed by the add-in: This exception with
the error message "Selected Collating Sequence Not Supported By The
Operating System" makes the whole connection unusable. So I can not simply
requery the table and retry but have to close the connection and open it
again. It is even better when I then open and close any table in Access
manually. After I do this I think I've never seen an exception thrown by
Update().

As I don't want to open and close a table in Access all the time to make
sure the add-in works I really wonder how I should debug this? I know that
the exception is thrown by OleDbDataAdapter.Update() but it is strange that
it happens only sometimes. It does not depend on the data that is changed so
I can't reproduce the exception. :-(

Boris

 

[Mary Chipman]

That is a tough one to troubleshoot. The lack of debugging tools and
the general flakiness of Access for a distributed application are a
main reason why many people opt to move to SQL Server. That being said
(which doesn't help you any), the rather cryptic error message you are
receiving may indicate some kind of data type mismatch. The fact that
you have to sometimes go in and manually open/close tables indicates
some kind of locking problem. I don't know what your code looks like,
but make sure you wrap all actions in a Try/Catch/Finally block where
the connection gets explicitly closed/disposed in the finally block.

--Mary

 

[Boris]

That is a tough one to troubleshoot. The lack of debugging tools and
the general flakiness of Access for a distributed application are a
main reason why many people opt to move to SQL Server. That being said
(which doesn't help you any), the rather cryptic error message you are
receiving may indicate some kind of data type mismatch. The fact that
you have to sometimes go in and manually open/close tables indicates
some kind of locking problem. I don't know what your code looks like,
but make sure you wrap all actions in a Try/Catch/Finally block where
the connection gets explicitly closed/disposed in the finally block.

Thanks for your answers, Mary! My Access add-in connects to the current
database, then iterates through all tables, loading them with
OleDbDataAdapter and DataTable, then changes data and writes it back to the
tables with Update(). After iterating through all the tables the connection
is closed. While the add-in is active a modal window is shown to provide
status information and to prevent users working with the database at the
same time. So there is just one connection to Access, and the modal window
avoids any race conditions with the user.

My add-in reads CurrentProject.Connection.ConnectionString and uses the same
connectionstring as Access does. The add-in doesn't open the database
exclusively or lock tables. Maybe I can change some parameters and try to
specifiy the connectionstring in some different ways - a wild guess however.
Any other ideas I could try?

Boris

[...]

 

[Mary Chipman]

I honestly don't know enough about your app to be able to make any
useful suggestions other than the ones I already have. If you think
it's an Access issue and not a .NET issue, you might want to ask
around on the Access newsgroups.

--Mary

That is a tough one to troubleshoot. The lack of debugging tools and
the general flakiness of Access for a distributed application are a
main reason why many people opt to move to SQL Server. That being said
(which doesn't help you any), the rather cryptic error message you are
receiving may indicate some kind of data type mismatch. The fact that
you have to sometimes go in and manually open/close tables indicates
some kind of locking problem. I don't know what your code looks like,
but make sure you wrap all actions in a Try/Catch/Finally block where
the connection gets explicitly closed/disposed in the finally block.

Thanks for your answers, Mary! My Access add-in connects to the current
database, then iterates through all tables, loading them with
OleDbDataAdapter and DataTable, then changes data and writes it back to the
tables with Update(). After iterating through all the tables the connection
is closed. While the add-in is active a modal window is shown to provide
status information and to prevent users working with the database at the
same time. So there is just one connection to Access, and the modal window
avoids any race conditions with the user.

My add-in reads CurrentProject.Connection.ConnectionString and uses the same
connectionstring as Access does. The add-in doesn't open the database
exclusively or lock tables. Maybe I can change some parameters and try to
specifiy the connectionstring in some different ways - a wild guess however.
Any other ideas I could try?

Boris

[...]