如何在MS Access中填充字段说明 [英] How to populate field descriptions in MS Access

问题描述

当通过ODBC(尤其是AS/400)链接到外部数据源时，我经常碰到另一边的隐秘字段名称，而该位置的数据字典不可用.在极少数情况下，我能够从另一个数据库获取字段描述，我希望能够一次将它们全部导入，而不是一次将每个描述复制/粘贴到表设计表单中./p>

我无法在系统表中找到它，所以我不知道此元数据存储在哪里.关于它的位置以及是否可以批量更新的任何想法?

更新:我设法使用OpenSchema方法读取架构(请参见下面的代码)，但这返回了只读数据集，这使我无法更新描述.

 函数UpdateFieldDescriptions()Dim cn作为新的ADODB.Connection作为ADODB.Recordset的昏暗Dim rs2作为记录集昏暗的strSQL作为字符串Dim strDesc作为字符串设置cn = CurrentProject.Connection设置rs = cn.OpenSchema(adSchemaColumns)虽然不是EOF如果Left(rs！table_name，4)<>然后"MSys"Debug.Print rs！table_name，rs！column_name，rs！DescriptionstrSQL =选择描述"&_"FROM tblColumn描述了一个&_"WHERE a.Name =""&rs！table_name&""AND"&_"a.Column =""&rs！column_name&";"设置rs2 = CurrentDb.OpenRecordset(strSQL)虽然不是rs2.EOFstrDesc = rs2.Fields(0)rs！Description = strDesc'<--这会产生错误温德万一rs.MoveNext温德rs.Updaters.CloseSet rs = Nothing设置rs2 =否设置cn =否结束功能 

解决方案

使用DAO代替ADO:

  Sub SetFieldDesc(TblName作为字符串，FldName作为字符串，描述作为字符串)Dim db作为DAO.Database，td作为DAO.TableDef，fld作为DAO.Field设置db = CurrentDb()设置td = db.TableDefs(TblName)设置fld = td.Fields(FldName)关于错误继续fld.Properties("Description")=说明如果Err.Number = 3270，则找不到属性".fld.Properties.Append fld.CreateProperty("Description"，dbText，Description)万一结束子 

When linking to an external data source via ODBC (especially an AS/400), I often run into cryptic field names on the other side, where a data dictionary is not available. In the rare event that I'm able to get the field descriptions from the other db, I would like to be able to import them all at once, rather than copy/paste each description into the table design form one at a time.

I wasn't able to find this in the system tables, so I don't know where this metadata is stored. Any ideas on where it is, and whether it can be updated in batch?

Update: I managed to read the schema using the OpenSchema method (see code below), but this returns a read-only dataset, making it impossible for me to update the descriptions.

Function UpdateFieldDescriptions()
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim strDesc As String

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaColumns)

While Not rs.EOF
    If Left(rs!table_name, 4) <> "MSys" Then
        Debug.Print rs!table_name, rs!column_name, rs!Description
        strSQL = "SELECT Description " & _
            "FROM tblColumnDescriptions a " & _
            "WHERE a.Name = """ & rs!table_name & """ AND " & _
            "a.Column = """ & rs!column_name & """;"
        Set rs2 = CurrentDb.OpenRecordset(strSQL)
        While Not rs2.EOF
            strDesc = rs2.Fields(0)
            rs!Description = strDesc ' <---This generates an error
        Wend
    End If
    rs.MoveNext
Wend

rs.Update
rs.Close
Set rs = Nothing
Set rs2 = Nothing
Set cn = Nothing

End Function

解决方案

Use DAO instead of ADO:

Sub SetFieldDesc(TblName As String, FldName As String, Description As String)
Dim db As DAO.Database, td As DAO.TableDef, fld As DAO.Field

    Set db = CurrentDb()
    Set td = db.TableDefs(TblName)
    Set fld = td.Fields(FldName)

    On Error Resume Next
    fld.Properties("Description") = Description
    If Err.Number = 3270 Then 'Property not found.'
        fld.Properties.Append fld.CreateProperty("Description", dbText, Description)
    End If
End Sub

这篇关于如何在MS Access中填充字段说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！