MS Access error "ODBC--call failed. Invalid character value for cast specification (#0)"

Question

Does anyone have an idea what this error means or how to solve it? I am using Access 2003 and SQL2005. It comes up when trying to add a record on a particular subform.

[Microsoft][SQL Native Client] Invalid character value for cast specification (#0)

This MS bug report describes the same message, but it is a bug in SQL Server 6.5 that has already been solved.

Solved: Apparently having no PK on the destination table was causing this, it didn't have anything to do with the subform or the query from Access. I wasn't even aware there were tables in this database without PK. Adding PK to the destination table solved it. The strange thing is the same query string that errored when executed via SQL native client, executed through SSMS with no errors. Hope this helps anyone else who has come across that strange message.

Answer 1

Hum, I would check the text box default on the access side. I would also bring up the linked table in design mode, and you want to check the data type that ms-access assumes here. For non supported data types ms-access will generally use a string, and sql server might be wanting something else.

So, check both the Primary key (PK) in main table, and then check the data type used (assumed) in the child table for the foreign key (FK) column. While we are at this, check your expressions used for the child/master link settings in the sub-form control (not the form, not the sub-form, but the sub-form control used in your form that links up these two tables).

Sub forms in access are sensitive if you don’t have a timestamp column in the sql server table. As mentioned check the PK and the FK data types and make sure they match up (just bring up the tables in design mode in ms-access -- you get an error message about the design mode being read only, but just continue on so you can check/view to ensure the data types match up).

So for the child table, you need a PK, a FK, and also a timestamp column (you don’t have to display the TS column in the sub-form, but you need it in the table).

Sub-forms in ms-access are sensitive and often fail if you don’t include a timestamp column in the sql table. (access uses these row version columns to determine if the data been changed).

Answer 2

Is one of your fields in the view calculated/built with the CAST function? In this case, you might not have the right to update/add a value for that field.

Can you execute your view in the MS SQL Studio interface and try to insert a record?

Answer 3

Another cause to this issue is that if you change a table name without alterting the view then the "Dependencies" of that view still remians with the table old name.

Let say I have a table 'A' and a view 'Av' which derives from 'A', and I created a new Table which will be named 'A' and I changed 'A's name to 'A_old' but I didn't executed an ALTER VIEW, so the dependencies of 'Av' still remain on 'A_old' but the view is derives from 'A' and it cuasing this Error in Access when trying to open the view as a linked table