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

Question

My users have a number of backend .accdb databases (which I can't access directly). I need to code some vba to modify the structure of some of the tables in these databases to convert Text Fields to Rich Text memos. (The fields already contain text including Access "rich-text" i.e. the relevant html coding).

I need to:

1. Modify the field to be a rich text memo.
2. Modify the existing contents (if applicable) to display correctly as Access rich text in forms, datasheets and reports.

I can write a SQl statement that will modify a field from TEXT (255) to MEMO:

ALTER TABLE tblSource ALTER COLUMN Detail1 MEMO

However, this leaves the resultant memo field as a plain text memo.

I have considered creating a new Rich Text Field and then copying the contents of the old one (using a SQL CREATE TABLE statement followed by an UPDATE statement that applies the Plaintext function to the contents of the old field and then copies the result to the new field, and then further SQl to delete the old field and rename the new) but can't find out how to create a rich-text memo (default seems to be plain text).

Extensive web searches haven't shown up any additional techniques I can deploy. This is a process that will be run once for each file, so it doesn't need to be elegant or quick but it does need to be bomb-proof!

Answer 1

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.