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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.