Insert on linked table (from MS Access to SQL Server) fails because of field length

I'm trying to do an append query from MS Access into SQL server. The SQL server column is varchar(max) which I thought meant it can accept more than 4000 characters

I get the following error when running this query from VBA in MS Access

Run Time Error 3155 ODBC - insert on a linked table failed [Microsoft] [SQL Server Native Client 10.0] {SQL Server] The size (7596) given to the parameter '@P6' exceeds the maximum allowed (4000). (#2717)

3 answers

  • answered 2019-09-10 02:29 yu2

    This is answer for your question

    3155 Insert into Linked Table error

    On this link Microsoft recommends to use ADODB instead of ODBC if you can't decrease field size in application.

    ODBC protocol is generally for big data -- sql server -- which uses "Fire Hose" bandwith. Access uses (my term) garden hose bandwith (with all due respect for mini RDBMSs) because Access is basically a mini RDBMS (relational database management system) which is also file based. Unless everything (front/back ends) is set up perfectly and conditions are ideal -- you will encounter the problem you are having. Microsoft came up with ADODB as a workaround for this problem. When I have to interface between sql server and Access -- I use ADODB. This has proven to be much more reliable and consistent between the small and large RDBMSs. Here is some sample ADODB code for reading from and writing to a Sql Server from Access

    '--add a reference in Tools/References to Microsoft ActiveX Data Objects 2.x Library '--(2.5 or higher) ...

  • answered 2019-09-10 06:26 david

    The Access "Long Text" column can contain a text string up to a gigabyte in size. The message says you are trying to fit 7596 characters into a 4000 character field.

    If so, your SQL server database should be exposing an ODBC LongVarChar column instead of VarChar.

  • answered 2019-09-10 06:26 John

    I think the error message is quite helpful.
    You are trying to fit a string with length of 7596 while your maximum varchar length is 4000.
    I guess you either truncate it or store it as a blob.