title: Parameterized INSERT with Memo and Text Fields in VBA / MS-ACCESS?
body: <p>I'm trying to do a simple parametrized insert with MS-Access 2007 and I keep getting the following error (3708) from <code>ADODB.Parameters</code>: </p>
  
  <p><code>Parameter object is improperly defined. Inconsistent or incomplete information was provided.
  s'</code></p>
  
  <p>I have 3 parameters that get inserted into 3 fields.  They are of the following types:</p>
  
  <ul>
  <li>query_name: TEXT (50)</li>
  <li>db_id: Number (Long Integer)</li>
  <li>query_text: Memo </li>
  </ul>
  
  <p><code>db_id</code> inserts just fine, but the other two text fields result in the error above.</p>
  
  <p>Below is my simple sample code:</p>
  
  <pre><code>Sub testz()
  
      Dim conn As ADODB.Connection
      Set conn = CurrentProject.Connection
  
      Dim adoCMD As ADODB.Command
      Dim adoRS As ADODB.Recordset
      Dim strSQL As String
      Dim lRecordsAffected As Long
  
      On Error GoTo Err_Insert
  
      strSQL = "INSERT INTO queries (query_name, db_id, query_text) VALUES (?, ?, ?)"
  
      Set adoCMD = New ADODB.Command
  
      With adoCMD
          Dim test As String
          test = "tetws"
          .ActiveConnection = conn
          .CommandType = adCmdText
          .CommandText = strSQL
  
          .Parameters.Append .CreateParameter("x1", adVarChar, adParamInput) ' Doesn't work - MS ACCESS Text field
          .Parameters.Append .CreateParameter("x2", adInteger, adParamInput) ' This works - Numeric field
          .Parameters.Append .CreateParameter("x3", adLongVarChar, adParamInput) ' This doesn't work MS ACCESS Memo field.
  
          .Parameters("x1").Value = test
          .Parameters("x2").Value = 56
          .Parameters("x3").Value = test
  
          adoCMD.Execute adExecuteNoRecords
      End With
  
      If lRecordsAffected = 0 Then
          Debug.Print ("------------------------")
          Debug.Print ("ERROR QUERY not inserted:")
          Debug.Print ("database id: " &amp; id)
          Debug.Print ("query name: " &amp; tblName)
          Debug.Print ("strSQL: " &amp; qryTxt)
          Debug.Print ("------------------------")
      Else
  
      End If
  
  Exit_Insert:
      Set adoCMD = Nothing
      Set adoRS = Nothing
  
      Exit Sub
  
  Err_Insert:
      Debug.Print "----------------"
      Debug.Print "BEGIN: Err"
      If err.Number &lt;&gt; 0 Then
          Msg = "Error # " &amp; Str(err.Number) &amp; " was generated by " _
           &amp; err.Source &amp; Chr(13) &amp; "Error Line: " &amp; Erl &amp; Chr(13) &amp; err.Description
          'MsgBox Msg, , "Error", err.HelpFile, err.HelpContext
          Debug.Print Msg
      End If
      Resume Exit_Insert
  
  
  End Sub
  </code></pre>
  
  <p>The <a href="http://www.carlprothman.net/?tabid=97" rel="nofollow">following reference</a> says that fields of the database type <code>MEMO</code>s should have a parameter type of <code>adLongVarChar</code> and that type database type <code>TEXT</code>, should have a type of <code>adVarChar</code>.</p>