10

How can I view the metadata(data dictionary) for a Microsoft Access 2013 table?

yesman
  • 109
  • 1
  • 1
  • 7

4 Answers4

7

If you want to retrieve the access database metadata this may be helpful:

Every microsoft access database contains a system table named MSysObjects. This tables contains this database metadata. You can get all objects with there creation date and last update date.

You can list all Objects in an Access Database using the following query:

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) <> '~'

If you don't want to show system objects you can add these conditions to the where clause:

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

enter image description here

Also i created an application that retrieve data from access database, Which i created a new Git-repository for it

Hadi
  • 36,233
  • 13
  • 65
  • 124
5

In Access 2007 and later (2007, 2010, 2013) the "Database Documenter" is under the Database Tools tab, in the Analyze group.Screenshot showing the button

Dai
  • 141,631
  • 28
  • 261
  • 374
2

Using, VBA, the DAO.TableDef object may help you:

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

This is quite over simplified, but you can get all the properties of the table and its fields.

Check:

Barranka
  • 20,547
  • 13
  • 65
  • 83
0

Late to the party, but this is what I did. It's not pretty but it gets the job done.

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
  • 115
  • 1
  • 7