如何查看Microsoft Access 2013表的元数据？

Question

我怎样查看 Microsoft Access 2013表格 的元数据（数据字典）？

- yesman

Answer 1

如果您想检索访问数据库的元数据，以下内容可能会有所帮助：

每个微软访问数据库都包含一个名为“MSysObjects”的系统表。该表包含此数据库的元数据。您可以获取所有对象及其创建日期和最后更新日期。

您可以使用以下查询列出访问数据库中的所有对象：

SELECT Name, DateCreate, DateUpdate,   
 iif(LEFT(Name, 4) = 'MSys','System Table', 
 iif(type = 2,'System Object',  
 iif(type = 3,'System Object', 
 iif(type = 8,'System Object',  
 iif(type = 4,'Linked Table (ODBC)', 
 iif(type = 1,'Table',  
 iif(type = 6, 'Linked Table (MsAccess/MsExcel)', 
 iif(type = 5,'Query',  
 iif(type = -32768,'Form', 
 iif(type = -32764,'Report',  
 iif(type=-32766,'Macro', 
 iif(type = -32761,'Module',  
 iif(type = -32756,'Page',  
 iif(type = -32758,'User','Unknown')))))))))))))) as ObjectType 
  FROM MSysObjects WHERE LEFT(Name, 1) <> '~' 

如果您不想显示系统对象，可以将以下条件添加到where子句中：

AND LEFT(Name, 4) <> 'MSys' AND Type IN (1, 5, 4, 6,  -32768, -32764, -32766, -32761,-32756,-32758)

我还创建了一个应用程序，从访问数据库中检索数据，我为此创建了一个新的Git存储库

注：以上内容为简化翻译，仅供参考。

- Hadi

Answer 2

在Access 2007及以后的版本（2007、2010、2013），"数据库文档生成器"位于"数据库工具"选项卡下的"分析"组中。

- Dai

Answer 3

使用VBA，DAO.TableDef对象可以帮助您：

dim db as DAO.Database, tbl as DAO.TableDef
dim f as DAO.Field
set db = currentdb() ' Connect to current database

' Loop through each table in the database
for each tbl in db.tableDefs
    debug.print "Table name: ", tbl.Name
    ' Loop throuth each field in the table
    for each f in tbl.Fields
        debug.print "Field: ", f.Name
    next f
next tbl

这有点过于简化，但您可以获得表及其字段的所有属性。

请检查以下链接：

- TableDef对象 (DAO) - Field对象 (DAO)

- Barranka

Answer 4

来晚了，但这就是我所做的。它不太美观，但能完成工作。

Sub GenerateDataDictionary()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    
    Dim DataDictionaryTableName As String
    DataDictionaryTableName = "__DataDictionary"
    
    Set db = CurrentDb
    
    If Not TableExists(db, DataDictionaryTableName) Then
        Set tdf = db.CreateTableDef(DataDictionaryTableName)
        With tdf
            .Fields.Append .CreateField("TableName", dbText, 255)
            .Fields.Append .CreateField("Position", dbLong)
            .Fields.Append .CreateField("ColumnName", dbText, 255)
            .Fields.Append .CreateField("Type", dbText, 255)
            .Fields.Append .CreateField("Size", dbLong)
            .Fields.Append .CreateField("Description", dbMemo)
        End With
        db.TableDefs.Append tdf
        Application.RefreshDatabaseWindow
    Else
        db.Execute "DELETE * FROM " & DataDictionaryTableName, dbFailOnError
    End If
    
    Set rs = db.OpenRecordset(DataDictionaryTableName)
    For Each tdf In db.TableDefs
        If tdf.Name = DataDictionaryTableName Then GoTo SkipTable
        If Left(tdf.Name, 1) = "~" Then GoTo SkipTable
        If Left(tdf.Name, 4) = "MSys" Then GoTo SkipTable
        If Left(tdf.Name, 4) = "USys" Then GoTo SkipTable
        If tdf.Connect <> "" Then GoTo SkipTable
        Select Case tdf.Name
            Case "Version", "VersionDetail"
                GoTo SkipTable
            Case Else
                For Each fld In tdf.Fields
                    With rs
                        .AddNew
                            !TableName = tdf.Name
                            !Position = fld.OrdinalPosition
                            !ColumnName = fld.Name
                            Select Case fld.Type
                                Case dbDate: !Type = "Date/Time"
                                Case dbText
                                    !Type = "Short Text"
                                    !size = fld.size
                                Case dbBoolean: !Type = "Yes/No"
                                Case dbMemo: !Type = "Long Text"
                                Case dbByte: !Type = "Int8"
                                Case dbInteger: !Type = "Int16"
                                Case dbLong: !Type = "Int32"
                                Case dbSingle: !Type = "Single"
                                Case dbDouble: !Type = "Double"
                                Case dbDecimal: !Type = "Decimal"
                                Case dbCurrency: !Type = "Currency"
                            End Select
                            On Error Resume Next
                            !Description = fld.Properties("Description")
                            On Error GoTo 0
                        .Update
                    End With
                Next fld
        End Select
    
SkipTable:
    Next tdf
    MsgBox "Done"
End Sub

Function TableExists(db As DAO.Database, TableName As String) As Boolean
    Dim tdf As DAO.TableDef
    For Each tdf In db.TableDefs
        If tdf.Name = TableName Then
            TableExists = True
            Exit Function
        End If
    Next tdf
    TableExists = False
End Function

- Dennis Jones

---

你用的是哪个版本的MS Access来编写这段代码？在Microsoft Access 2021中，调用'TableExists'函数时会出现错误。 - user3276159

非常抱歉。我已经更新了代码，添加了TableExists函数。 - Dennis Jones