In Access you can have a Memo field that holds up to 64k characters, give or take. This is great for adding comments and notes.
The SSMA converts those as nvarchar(max) and correctly moves the data, however when linking to it using the SQL Native Client, Access thinks it is a text(255) field and displays only the last 255 characters of the field. Not good.
The fix is that you have to change the data type to nvarchar(4000) and relink; Access will then retrieve the entire field (up to 4,000 chars of course). In the event that you have Memo fields that exceed 4,000 characters - good question. You might want to consider saving those on the file system & storing their locations in SQL.
The safest way to make the table change:
- using SSMS, open the table in design view
- make the change but don't save it
- click the script icon to save the table change as a T-SQL file
- close the table without saving the change
- make a backup of your database - !
- apply the script
- relink the Access front-end
- test
1 comment:
I had the same problem but if you use to link the talbes the driver SQL Server instead of SQL Native Client, the memo fields are migrated properly.
Post a Comment