"Invalid Argument" error editing / updating data with Access front end and SQL Server

[AshleyWells]

Im getting the error "Invalid Argument" suddenly with some of my tables in SQl Server 2008. Im using Access as my front end but when i open the table within Access cetain records error when trying to make a change. There are around 22,000 records in the table but it only seems to happen on about half a dozen. I've checked primary keys etc but that's not causing it. I have tried editing the data in the table, using an update query to change the data and used the CurrentDB.Execute code but they all error. I have deleted the table and re-created it but still no joy. Can anyone help?

[rpeare]

are you linking to a table, or to a view?
if it's a view, are there calculated values in that view and if so are any of the expressions potentially a problem? (i.e. dividing by zero, or performing a date function on a text field)

Can you identify which records are causing the problem and go to the table to see what's actually been stored? have you examined the records in your SQL tables for non-visible ascii (for example) characters (like line feed, carriage return)?

[tanvi]

There are various reasons behind this error. Some common reasons are database corruption & database size limit. Please check this article for more information: http://data-base-recovery.blogspot.c...ror-in-ms.html

[AshleyWells]

Im linking to the table from an Access front end and it has no relationships to other tables.
I have managed to find out which of the fields im having an issue with and written some code to strip out any non alphanumeric charaters (as it's a text field) but that didnt solve it either. The field is an nvarchar(255) and the text that was entered into the records that are causing this issue are 255 charaters long. I wasnt sure if that was causing the problem so i limited the length of text available to enter into the field to 200 and it seems to have solved the it.
Does SQL server need to add any hidden characters to text fields or anything like that?

[rpeare]

yes there's likely an end of line character but you can strip those out with the trim([string]) function, but if you're linking to the table directly you would have to run an append or update query rather than a link which is probably not the greatest solution to your problem