This is a brand new development. I am in the Architectural Design phase. Access 2007 front end. Front end will be compiled and will connect via ODBC to a SQL Server 2008 R2 back end, which is also under development. There is no legacy data to deal with or any legacy Access database to migrate. This is a totally new, clean, and empty development. My client wishes to make extensive use of memo fields so my question here is preventative in nature.
The MSDN recommendation and SSMS recommendation is to map the Access Memo field to a SQL Server nvarchar(max) and NOT to varchar(max), text, or anything else. I have searched a number of sites and MSDN and see lots of problems mentioned even with nvarchar(max), with no clear solutions. One mentioned an ODBC limit of 8000 bytes. Most mention truncation problems past 255 characters related to a variety of issues.
What I don't see so far is a clear, "Do this and it works great!" answer.
There is no code or data to show you. I am looking for insight from the 20,000 foot level.
An ounce of prevention is worth a pound of support, says it all.
Phred