VBA设置FORMAT属性时触发Data Type Conversion Error问题

问题原因 #

触发「Data Type Conversion Error」的核心原因是：CreateProperty方法的第二个参数指定的是属性本身的数据类型，而非字段的数据类型。

• Format是字符串类型的属性，需要用dbText（对应数值10）作为类型参数，而不是Double字段的类型值dbDouble（7）。
• DecimalPlaces是整数类型的属性，需要用dbInteger（3）作为类型参数，同样不能用字段的DataTypeNo。
• 代码里还存在文本字段属性名写错的问题：把"FieldSize"写成了"vrFieldFormat"，且类型参数也错误。

修正后的代码 #

Sub test()
    fnReports 4
End Sub

Function fnReports(vrReportNo As Long)

    Dim db As DAO.Database
    Dim rsS As DAO.Recordset
    Dim vrSQL As String
    Dim vrTableName As String
    
    'create report table
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
 
    Set db = CurrentDb
    vrTableName = "mtMDAR" & vrReportNo
    
    vrSQL = "SELECT MDARReportFields.SortNoField, MDARReportFields.FieldName, MDARReportFields.FieldSize, FieldFormats.FieldFormat " & _
            ", MDARReportFields.FieldDecimalNo, FieldDataTypes.FieldDataTypeNo, FieldDataTypes.FieldDataType, FieldDataTypes.FieldDataTypeVBA " & _
            "FROM FieldFormats RIGHT JOIN (FieldDataTypes RIGHT JOIN MDARReportFields ON FieldDataTypes.FieldDataTypeID = MDARReportFields.FieldDataTypeID) " & _
            "ON FieldFormats.FieldFormatID = MDARReportFields.FieldFormatID WHERE MDARReportFields.ReportNo=" & vrReportNo & "  ORDER BY MDARReportFields.SortNoField"
    
    fnDeleteObjectIfExists "table", vrTableName
    Set tdf = db.CreateTableDef(vrTableName)
    
    Dim vrFieldName As String
    Dim vrFieldSize As Long
    Dim vrFieldFormat As String
    Dim vrFieldDecimalNo As Long
    Dim vrFieldDataTypeNo As Long
    Dim vrFieldDataTypeVBA As String
 
    Set rsS = db.OpenRecordset(vrSQL, dbOpenSnapshot)
   
    ' 第一步：创建所有字段
    Do Until rsS.EOF
        vrFieldName = rsS!FieldName
        vrFieldDataTypeNo = rsS!FieldDataTypeNo

        With tdf
            Set fld = .CreateField(vrFieldName, vrFieldDataTypeNo)
            .Fields.Append fld
        End With
        rsS.MoveNext
    Loop
 
    db.TableDefs.Append tdf
    
    ' 第二步：设置字段属性
    rsS.MoveFirst
    Do Until rsS.EOF
        vrFieldName = rsS!FieldName
        vrFieldDataTypeVBA = rsS!FieldDataTypeVBA
        vrFieldDataTypeNo = rsS!FieldDataTypeNo
        
        If vrFieldDataTypeVBA = "dbText" Then
            vrFieldSize = rsS!FieldSize
        Else
            vrFieldFormat = rsS!FieldFormat
            vrFieldDecimalNo = rsS!FieldDecimalNo
        End If
    
        With tdf
            Set fld = .Fields(vrFieldName)
         
            If vrFieldDataTypeVBA = "dbText" Then
                ' 设置文本字段长度：属性类型为dbInteger
                fld.Properties.Append db.CreateProperty("FieldSize", dbInteger, vrFieldSize)
            Else
                ' 设置小数位数：属性类型为dbInteger
                fld.Properties.Append db.CreateProperty("DecimalPlaces", dbInteger, vrFieldDecimalNo)
        
                ' 设置Format属性：属性类型为dbText
                If vrFieldFormat = "percent" Or vrFieldFormat = "Standard" Then
                    fld.Properties.Append db.CreateProperty("Format", dbText, vrFieldFormat)
                End If
            End If
        End With
        rsS.MoveNext
    Loop
 
    ' 清理对象
    Set tdf = Nothing
    rsS.Close
    Set rsS = Nothing
    Set db = Nothing
End Function

关键修正点 #

• Format属性：统一使用dbText作为类型参数，确保字符串格式的属性值能正确写入。
• DecimalPlaces属性：使用dbInteger作为类型参数，匹配该属性的整数类型要求。
• 修正文本字段的属性名错误：将"vrFieldFormat"改为正确的"FieldSize"，并使用dbInteger作为类型参数。
• 调整了记录集的重置：在第二次循环前添加rsS.MoveFirst，避免因记录集指针在末尾导致循环不执行。

内容的提问来源于stack exchange，提问作者Nata