Parameterized INSERT with Memo and Text Fields in VBA / MS-ACCESS?

Question

I am trying to do a simple parameterized insert with MS-Access 2007, and I continue to get the following error (3708) from ADODB.Parameters :

Parameter object is improperly defined. Inconsistent or incomplete information was provided. s'

I have 3 parameters that are inserted in 3 fields. They have the following types:

• query_name: TEXT (50)
• db_id: Number (long integer)
• query_text: Memo

db_id only inserts a penalty, but the error above appears in the other two text fields.

Below is my simple code example:

 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: " & id) Debug.Print ("query name: " & tblName) Debug.Print ("strSQL: " & 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 <> 0 Then Msg = "Error # " & Str(err.Number) & " was generated by " _ & err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & err.Description 'MsgBox Msg, , "Error", err.HelpFile, err.HelpContext Debug.Print Msg End If Resume Exit_Insert End Sub 

The following link says that the fields of the MEMO database type must be of the adLongVarChar parameter adLongVarChar and the TEXT database type must be of the type adVarChar .

Answer 1

I believe that there are two problems here:

• I am sure that with the release of Jet 4.0 (and, subsequently, ACE), the required types have changed to adVarWChar for Text , and adLongVarWChar for Memo (for multi-byte character support).

• Text parameters must have a (maximum) length. I do not see them in your .CreateParameter() calls.