none
Insert more than 255 chars in a memo field RRS feed

  • Question

  • Good morning,
    I'm trying to insert data into an existing Ms Access database (.mdb file). When I try to insert programmatically a new record in a table I have a problem with a memo field. It is returned this error:

    "Unable to add the desired amount of data. The field is too small. Try to insert or paste less data"

    I have Googled a lot for this problem but I have found no acceptable solutions for this problem.
    Here the queries used:

    1) INSERT INTO tblXXX ([note]) VALUES (?)
    2) UPDATE tblXXX SET [note]=? WHERE YYY=?

    Here the parameters added:

    cmd.Parameters.Add("@note", OleDbType.LongVarWChar).Value = part.note;
    cmd.Parameters.Add("@YYY", OleDbType.Char).Value = part.YYY; // Only for the 2nd query

    I have tried/checked the following steps:
    1) Checked the type of the used parameter (OleDbType.LongVarWChar), tried also other types without success
    2) Updated OLEDB drivers to version 16 x64, and updated connection string to use this driver
    3) Created a new .accdb database and imported all tables from the .mdb
    4) Tried to modify the first 8 rows in the table tblXXX, inserting more then 255 chars in the db
    5) Tried to convert the text format of the Memo field to "RTF" (standard value is "normal text")

    But the problem remains, I can insert or update only 255 chars. Can you help me to solve this issue? I have no more tries!
    Thanks in advance

    Paolo Bozzoli

    Paolo

    Tuesday, June 28, 2016 12:59 PM

Answers

All replies

  • Open the tblXXX table in design mode and make sure the fields Data Type is 'Memo'.

    Tuesday, June 28, 2016 3:04 PM
  • >>>I'm trying to insert data into an existing Ms Access database (.mdb file). When I try to insert programmatically a new record in a table I have a problem with a memo field. 

    According to your description, I suggest that you could use below VBA code to insert or update table Memo field, do you get any errors?
    content = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
       "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    
    CurrentDb.Execute _
      "UPDATE  tblXXX " & _
      "SET [Note] = '" & content & "' " & _
      "WHERE [YYY]= '" & "xxx" & "'"
       
    CurrentDb.Execute _
      "INSERT INTO  tblXXX ([note]) VALUES ('" & content & "')"

    Wednesday, June 29, 2016 6:28 AM