AccessDB ODBC link to SQL table turns nvarchar(MAX) into memo, causing crash

  • Hi everyone - I realize this is probably more of an Access problem than a SQL Server problem, and have posted on Access forums , but wondered if anyone else had run into this. So thanks for bearing with what is probably an Access question? but might affect those using SQL databases, too.

    I have an Access 2010 table with an ODBC link to a SQL Server 2012 table.

    In the SQL table, a number of fields are nvarchar(MAX).

    In Access's (read-only) design view, they show up as MEMO fields. Since one is used in a WHERE clause, Access predictably fails on a MEMO field comparison. Other nvarchar(MAX) fields appear as MEMO, too, which is why I think that nvarchar(MAX) is the culprit.

    So, I changed the necessary field to text in the SQL database and relinked the table.

    Still MEMO. Still going to crash.

    Any ideas what is going on under the hood and how to tinker with it? This problem is occurring in development, as production has older versions of SQL Server and Access that run just fine, which is what leads me to believe it might be related to updated versions.

  • ebowditch (11/20/2014)


    Hi everyone - I realize this is probably more of an Access problem than a SQL Server problem, and have posted on Access forums , but wondered if anyone else had run into this. So thanks for bearing with what is probably an Access question? but might affect those using SQL databases, too.

    I have an Access 2010 table with an ODBC link to a SQL Server 2012 table.

    In the SQL table, a number of fields are nvarchar(MAX).

    In Access's (read-only) design view, they show up as MEMO fields. Since one is used in a WHERE clause, Access predictably fails on a MEMO field comparison. Other nvarchar(MAX) fields appear as MEMO, too, which is why I think that nvarchar(MAX) is the culprit.

    So, I changed the necessary field to text in the SQL database and relinked the table.

    Still MEMO. Still going to crash.

    Any ideas what is going on under the hood and how to tinker with it? This problem is occurring in development, as production has older versions of SQL Server and Access that run just fine, which is what leads me to believe it might be related to updated versions.

    It's defaulting to MEMO since it's the closest access data type that can "fit" the max size of a nvarchar(max). Since text can only acomodate up to 255, anything larger gets tagged as a memo.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • thanks, that is the top level answer, but that doesn't explain why changing the underlying SQL data type from nvarchar(max) (which was overkill for a 2-character field)

    to text, or nvarchar(50) or something that is quite convertible

    still shows up as MEMO in Access. after relinking, quitting and restarting, etc.

    But this is most likely an Access issue, as I said, just wondered if anyone else had bumped up against this and had a solution from the SQL side.

  • ebowditch (11/21/2014)


    thanks, that is the top level answer, but that doesn't explain why changing the underlying SQL data type from nvarchar(max) (which was overkill for a 2-character field)

    to text, or nvarchar(50) or something that is quite convertible

    still shows up as MEMO in Access. after relinking, quitting and restarting, etc.

    But this is most likely an Access issue, as I said, just wondered if anyone else had bumped up against this and had a solution from the SQL side.

    oh I misread a little there. I understand why the SQL TEXT data type wouldn't help, since it's the direct equivalent of ACCESS' memo data type.

    I think the issue you're left with is using the VARCHAR(50) data type rather than NVARCHAR(50). The unicode support tends to confuse things a bit (MEMO isn't really geared to understand how to support extended characters/multiple character sets, etc...). I have made extensive use of SQL linked tables in various Access applications, but as I mentally went back over those items - all of the ones in play did NOT leverage NVARCHAR, only VARCHAR.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks! - that makes sense. I will definitely file it for future development/data migration that is on the horizon.

    The other way around this (speaking to the original application programmer) was to return to the point where the original Access data gets loaded into SQL ( a weekly process) and change the MEMO fields to text there so that SQL never "knows" it was MEMO. It imports into table, and all the (dozens) of views work fine after that. Or, preprocess the MEMO, I suppose, would work too. That way there's no altering the underlying DB structure (which I attempted, but introduced other problems, so backed away from that solution.)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply