How to Convert a Text Field in an Access Table to a Rich Text Memo Using Vba

How to convert a text field in an Access table to a rich text memo using VBA

Since Rich Text is not a datatype and is not a field property which can be defined or modified with a SQL statement, you will need VBA to set the field's TextFormat property.

You can adapt techniques from this code sample.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("Table1")
Set fld = tdf.Fields("memo_fld")
Debug.Print "acTextFormatPlain: " & acTextFormatPlain & _
    "; acTextFormatHTMLRichText: " & acTextFormatHTMLRichText
With fld.Properties("TextFormat")
    Debug.Print "TextFormat: " & .Value
    If .Value = acTextFormatPlain Then
        .Value = acTextFormatHTMLRichText
        Debug.Print "TextFormat changed to: " & .Value
    End If
End With

Note that code is run from the database which contains the target table. If Table1 was actually a link to a table in another Access db file, the code would fail.

Note also that only applies to a memo field. The TextFormat property is not created for regular text datatype fields, so this will throw error #3270, "Property not found."

Debug.Print tdf.Fields("some_text").Properties("TextFormat").Value

Since you will be converting regular text fields to memo fields, that point is probably not a concern. I mentioned it only in case you stumble into it.

ColeValleyGirl discovered the TextFormat property is not always created for a new memo field.

Access Make table with memo field as column output instead of Text

The answer was to append to the existing table (clearing it first), and setting the concatRelated column from Group By to Expression.

MS Access VBA to update table field with rich-text textbox value

AH! I figured it out. Hope this is useful to others:

I had only set the textbox control on the form to Rich Text. I needed to also go into the target data table itself (tblSupeGenNotes) and set the [Note] field to have a "Text Format" property of "Rich Text" instead of "Plain Text".

After making that change, the rich text displays properly on the form, and, the text with formatting is correctly transferred into the target table.

How to add rich text column to Access database table using C# and OleDB?

How can we add a Rich text column to a table programmatically in C# using OleDb API?

We can't do it using OleDb. We need to use Access DAO for that:

// required COM reference: Microsoft Office 14.0 Access Database Engine Object Library
//
// using Microsoft.Office.Interop.Access.Dao; ...
var dbe = new DBEngine();
Database db = dbe.OpenDatabase(@"C:\Users\Public\Database1.accdb");
TableDef tbd = db.TableDefs["MyTable"];  // existing table
var fld = new Field();
fld.Name = "MyRichMemo";
fld.Type = (short) DataTypeEnum.dbMemo;
tbd.Fields.Append(fld);
// now set "Text Format" property to "Rich Text"
fld.Properties.Append(fld.CreateProperty("TextFormat", DataTypeEnum.dbByte, 1));
db.Close();

MSACCESS: How to update a memo field with large text

You can use recordsets to update the field. That way, you won't exceed the maximum length of an update query

(incomplete code, need more details to write more exact code)

Dim str As String
'Read text file into str
Dim rs As DAO.RecordSet
Set rs = CurrentDb.OpenRecordset("MyTable")
rs.AddNew
rs.Fields("MyMemoField").Value = str
rs.Update
rs.Close

Textbox with TextFormat= Rich Text doesn't work in 2013

You misunderstand what the Rich Text property does. Rich Text does not use RTF, it uses a limited form of HTML (allowing tags like <i>, <b>, etc.).

If you want to use RTF, you can either work with RTF files and an OLE object, or use a custom .ocx.

Stephen Lebans has written an RTF control, that can be found here (no affiliation). I personally haven't used it, but feel free to try.

---

Related Topics