PRB: "Syntax Error" with SQL Literal String Over 16,379 Bytes

ID: Q194127

The information in this article applies to:

Microsoft ODBC Driver for Access, versions 1.0, 2.0, 3.0, 3.5, 3.6

SYMPTOMS

If you attempt to execute a SQL INSERT statement that contains a literal string having over 16,379 characters with the Microsoft ODBC Driver for Access, the following error message occurs:

   [Microsoft][ODBC Microsoft Access 97 Driver]
   Syntax error in INSERT INTO statement.

SQL UPDATE SQL statements with literal strings over 16,379 bytes also fail with a similar error.

CAUSE

This behavior is by design.

The SQL parser for the Microsoft ODBC Driver for Access does not accept string literals larger than 16,379 bytes. This is the defined limit for literal strings in a SQL statement for the Microsoft ODBC Driver for Access.

RESOLUTION

Use parameterized insert or updates rather than embedding literal strings into the SQL statements if you expect to insert or update large string values.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

Run the following sample code to reproduce the problem and demonstrate the workaround. Note that the sample requires a blank Microsoft Access database named c:\db1.mdb and a reference to the Microsoft ActiveX Data Objects Library.

Sample Code

   ' START SAMPLE CODE
   Sub TestMemo()
   Dim conn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim strSQL As String

      conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=c:\db1.mdb"
      conn.BeginTrans
      On Error Resume Next
      conn.Execute "DROP TABLE tmp_MEMO1234"
      On Error GoTo 0
      conn.Execute "create table tmp_MEMO1234 (data1 memo)"

      ' Using a parameterized insert works.
      cmd.ActiveConnection = conn
      cmd.CommandText = "insert into tmp_MEMO1234 (data1) values (?)"
      cmd.CommandType = adCmdText
      cmd.Parameters.Append cmd.CreateParameter("p1", _
                            adLongVarChar, adParamInput, -1)
      cmd.Parameters(0).Value = String(16380, "X")
      cmd.Execute ' SUCCESS

      ' Literal string 16379 bytes or less works.
      strSQL = "insert into tmp_MEMO1234 (data1) values ('" & _
               String(16379, "X") & "')"
      conn.Execute strSQL ' SUCCESS

      ' Literal string 16380 bytes or larger fails.
      strSQL = "insert into tmp_MEMO1234 (data1) values ('" & _
               String(16380, "X") & "')"
      conn.Execute strSQL ' FAILS HERE

   End Sub
   ' END SAMPLE CODE

Additional query words: kbDriver kbJET kbODBC kbDatabase

Version           : WINDOWS:1.0,2.0,3.0,3.5,3.6
Platform          : WINDOWS
Issue type        : kbprb
Solution Type     : kbnofix

Last Reviewed: October 14, 1998