Post reply

varchrar(max)/nvarchar(max) truncated to 255 characters when imported into Access memo field

  • December 9, 2015 at 7:42 am

    #399442

    When a varchar(max) field is imported via ADO into local Access table memo field it is truncated to 255 characters. I cast varchar(max) to TEXT - then it works OK. I'm now in SS2K8, however it's going to be upgraded to SS2014 - I learned that TEXT is not anymore.

    1. Is it true?

    2. Any idea what may be the solution?

    Thanks

  • December 9, 2015 at 8:08 pm

    #1845091

    Data type binding/mapping is performed by the provider. For your situation that provider should be a Jet driver. Please check its documentation and/or post your question on an Access or Jet forum :-).

  • December 9, 2015 at 11:33 pm

    #1845100

    It may also be just a display problem. For example, the default display column width in SQL Server is only 255 bytes and it's a bit of a bugger to try to display more than 8,000 characters. Check the length of the column to verify what the length of the actual data is. In Access, I believe the function is LENGTH() but I don't remember for sure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 9, 2015 at 11:41 pm

    #1845102

    that would be too easy... it's LEN()

  • December 10, 2015 at 9:23 am

    #1845278

    The bottomline is: Access memo truncates varchar(max) to 255 but fully accepts cast(fieldVarcharMax as Text)

  • December 10, 2015 at 10:08 am

    #1845289

    Actually, the TEXT data type is still available in SQL Server 2014. Yes, it has been deprecated since 2005, but until Microsoft removes the TEXT data type from all it own internal code/tables you may still have access to the data type. Just don't use it in any SQL Server development.

  • December 10, 2015 at 11:21 am

    #1845304

    SoHelpMeCodd ==>

    In my codes i'm using ado - the sql code runs on the server

  • December 10, 2015 at 11:25 am

    #1845306

    Thank you Lynn. What does it mean not to use TEXT in new developments? In new queries?

Viewing 8 posts - 1 through 7 (of 7 total)

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