如何使用 VBA 将 Access 表中的文本字段转换为富文本备忘录 [英] How to convert a text field in an Access table to a rich text memo using VBA

问题描述

我的用户有许多后端 .accdb 数据库(我无法直接访问).我需要编写一些 vba 代码来修改这些数据库中某些表的结构，以将文本字段转换为富文本备忘录.(这些字段已经包含文本，包括访问富文本"，即相关的 html 编码).

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).

我需要:

1. 将该字段修改为富文本备忘录.
2. 修改现有内容(如果适用)以在表单、数据表和报告中正确显示为 Access 富文本.

我可以编写一个 SQL 语句，将一个字段从 TEXT (255) 修改为 MEMO:

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.

我考虑过创建一个新的富文本字段，然后复制旧字段的内容(使用 SQL CREATE TABLE 语句后跟一个 UPDATE 语句，该语句将纯文本函数应用于旧字段的内容，然后复制结果到新字段，然后进一步SQl删除旧字段并重命名新字段)但找不到如何创建富文本备忘录(默认似乎是纯文本).

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!

推荐答案

由于 Rich Text 不是数据类型，也不是可以用 SQL 语句定义或修改的字段属性，您将需要 VBA 来设置字段的 TextFormat 属性.

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

请注意，代码是从包含目标表的数据库中运行的.如果 Table1 实际上是指向另一个 Access db 文件中的表的链接，则代码将失败.

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.

另请注意，这仅适用于备注字段.TextFormat 属性不是为常规文本数据类型字段创建的，因此这将引发错误 #3270，未找到属性."

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

由于您要将常规文本字段转换为备注字段，因此这一点可能不是问题.我提到它只是为了防止你偶然发现它.

ColeValleyGirl 发现 TextFormat 属性并不总是为新的备注字段创建.

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

这篇关于如何使用 VBA 将 Access 表中的文本字段转换为富文本备忘录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！