Changing nvarchar Field size in SQL server 2014 adds empty space to table linked to MS Access 2010; ODBC

Hi guys

I think the subject tell them all.....
I have a SQL server table which i've changed the field size to bigger. what happened is that it adds spaces (which is visible only in MS Access) and cannot be removed from access  nor SQL server. any suggestions ?

Thanks in advance
Joseph KrauszCEOAsked:
Who is Participating?
What length is the nvarchar column?

Have you tried removing linked tables, then do a compact & repair on Access DB before re-adding them?

Is it one space at the end of the text?

How is it 'visible' in Access? Is it only there when you move the cursor to the end of the field in form and/or datasheet view?

Note that native Access tables will automatically trim trailing spaces from text values whereas nvarchar columns in linked MSSQL tables will not be auto-trimmed. So, if you enter text in a control manually or via code and the string has trailing spaces, when you store that in the nvarchar column it will not be auto-trimmed as it would be in a native Access table.

From SSMS, what is does DATALENGTH(theColumnName) return - is it 2x the value of LEN(theColumnName)?
Quick fix: Use the linked table manager to refresh the linked tables.

If that doesn't work, note the name of the linked tables, delete them all from Access and then add them all again. Table design changes often cause problems because the metadata for already linked tables isn't updated in Access, so really is best to remove and re-link them.
Joseph KrauszCEOAuthor Commented:
Doesn't help
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
make sure you are using nvarchar and not varchar,  the varchar data type will display exactly the number of characters in the field definition, right padded with spaces.
Hi Dale,

that's not correct, the difference between nvarchar and varchar is only that nvarchar saves two bytes for each character (=Unicode) and varchar one. What you meant is the char datatype (which also has a nchar pendant), both filling the rest of the field with spaces (fixed width datatype).

nvarchar should automatically cut off spaces at the right side as it should only save the number of characters entered (that's the "var" in the name) independent of Access or other frontends.


Joseph KrauszCEOAuthor Commented:
One point were missing. which is -  after making the compact i need to make a "trim" update in order to remove the spaces.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.