Opening Microsoft Access files in TIBCO Spotfire Analyst fails with error "The provider could not determine the Int16 value."

Opening Microsoft Access files in TIBCO Spotfire Analyst fails with error "The provider could not determine the Int16 value."

Sometimes, opening an Microsoft Access (.accdb) data file in TIBCO Spotfire may fail with the below error:

---------
InvalidOperationException at System.Data:

The provider could not determine the Int16 value. For example, the row was just created, the default for the Int16 column was not available, and the consumer had not yet set a new Int16 value. (HRESULT: 80131509)
---------

Below is the details stack trace:

Error message: Could not open data source.

TargetInvocationException at Spotfire.Dxp.Framework:
Exception has been thrown by the target of an invocation. (HRESULT: 80131604)

Stack Trace:

at Spotfire.Dxp.Framework.ApplicationModel.ProgressService.ExecuteWithProgressCancelable(String title, String description, ProgressOperation operation, Boolean showCancelButton)
...
at Spotfire.Dxp.Forms.Framework.Utilities.LoadFileHelper.LoadFile(LoadCallback callback, LoadCallbackParameters parameters, Form owningForm)

ImportException at Spotfire.Dxp.Data:

The provider could not determine the Int16 value. For example, the row was just created, the default for the Int16 column was not available, and the consumer had not yet set a new Int16 value. (HRESULT: 80131500)

Stack Trace:

at Spotfire.Dxp.Data.Import.OutOfProcessDataRowReader.TryExecute(Action f)
at Spotfire.Dxp.Data.Import.OutOfProcessDataRowReader.TryExecute[T](Func`1 f)
...
at Spotfire.Dxp.Forms.Application.FormsApplicationThread.<>c__DisplayClass1.<ExecutePromptingOperation>b__0()
at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

InvalidOperationException at System.Data:

The provider could not determine the Int16 value. For example, the row was just created, the default for the Int16 column was not available, and the consumer had not yet set a new Int16 value. (HRESULT: 80131509)

Stack Trace:

at System.Data.OleDb.ColumnBinding.ValueDecimal()
at System.Data.OleDb.OleDbDataReader.GetDecimal(Int32 ordinal)
...
at Spotfire.Dxp.Data.Import.DataSourceOutOfProcessConnection.CallbackConnection.ExecuteQueryCore(QueryResultWriter resultWriter, Int32 maxRows)
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.SubprocessImpl..ctor(String[] args)

This problem usually occurs during concatenation. The exception could be thrown if you are writing a custom SQL query that uses Microsoft Access concatenation operators to append two or more data columns together which are of different data types (e.g. concatenation of a string column with an Integer column). To avoid this, you would need to identify the data column which could be causing the issue and cast it explicitly (using the Microsoft Access cast functions) in your custom SQL query.

The exception could also be thrown while trying to access a data table with a column value that is no more available. 
  http://www.symantec.com/business/support/index?page=content&id=TECH127565
http://social.msdn.microsoft.com/Forums/en-US/4ba44f06-088d-495a-aff7-6f6f13619c14/oledb40-the-provider-could-not-determine-the-doublevalue-when-trying-to-query-average
http://bytes.com/topic/access/answers/210216-how-convert-integer-string