Microsoft Access Append Querydef for Memo Field

Question

I am getting an vba error 3271; Invalid property value. This happens when trying to append a memo field in a querydef. Any ideas on how to get around this?

Example:

public sub TestMemoField
    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("AppendRecord")
    qdf.Parameters("@SomeBigText").value = string(1000,"A")
    qdf.Execute
end sub

Thanks in advance.

Answer 1

Apparently you cannot have a parameter longer than 255 characters ( http://support.microsoft.com/kb/275116 ).

It is possible to use a recordset, or to use:

qdf.SQL="INSERT INTO Sometable (SomeField) Values('" & String(1000, "A") & "')"

Answer 2

Um, what are you trying to do? Why are you using parameters? Why not just execute SQL in code, like this:

Public Sub TestMemoField
  Dim strSQL As String

  strSQL = "UPDATE MyTable SET MyField='" & String(1000,"A") & "'"
  CurrentDb.Execute strSQL, dbFailOnError
End Sub

I don't use parameters in saved queries except when I need to pull a value from a control on a form to be used in a saved query.

Now, my answer might not be good if your back end is not Jet or if there's something about the actual criteria and structure of your saved query that makes it important to use a saved query instead of simply using on-the-fly SQL. But you've provided virtually no information (including omitting the SQL of the querydef you're executing), so it's rather difficult to supply any kind of helpful answer.