How do I create a Data Dictionary in Access 2

[Sherman6789]

Hello,

Can anyone tell me how to make an Access "Data Dictionary" or locate one already created? We need one that list all tables, fields, relationships, reports, conflicts, names, dates, etc. We had many of these in the old dBase III world, but I don't see any in Access nor do I hear anyone that seems to be concerned.

Thanks!

William Sherman

 

[RoyVidar]

Hi!

You should be able to find something of interest, I believe, in Tools | Analyze | Documenter (available, at least from access 2000)

HTH Roy-Vidar

 

[jrbarnett]

Here's one I wrote that doesn't include many of the excessive details of the Microsoft standard documenter:

http://www.jrbarnett.freeserve.co.uk/mdbdoc.htm

John

 

[Quehay]

Here's one I wrote, use, and love. It will drop the table each time it's run so your Data Dictionary is current.

*The error number for "table not found" error seems to vary with each different MDAC, so look for that on the first run and update accordingly.

[tt]
Public Sub DocumentTables()
'Requires function FieldType

On Error GoTo Error_DocumentTables

Dim DB As DAO.Database
Dim Rs As DAO.Recordset
Dim TDF As DAO.TableDef
Dim FLD As DAO.Field
Dim strSQL_DROP As String
Dim strSQL_CREATE As String

'Delete existing copy of this table
strSQL_DROP = "DROP TABLE data_dictionary;"

DoCmd.RunSQL strSQL_DROP

strSQL_CREATE = "CREATE TABLE data_dictionary" & _
"(table_name varchar(50),table_description varchar(100), field_name varchar(50),field_description varchar(100)," & _
"ordinal_position NUMBER, data_type varchar(15)," & _
"length varchar(5), default varchar(30), primary_key varchar(10)," & _
"nullable varchar(20));"

DoCmd.RunSQL strSQL_CREATE

Set DB = CurrentDb()

Set Rs = DB.OpenRecordset("data_dictionary&quot

With Rs
For Each TDF In DB.TableDefs
If Left(TDF.NAME, 4) <> &quot;Msys&quot; Then

For Each FLD In TDF.Fields
.AddNew
!table_name = TDF.NAME
!table_description = TDF.Properties(&quot;description&quot
!field_name = FLD.NAME
!field_description = FLD.Properties(&quot;description&quot
!ordinal_position = FLD.OrdinalPosition
!data_type = FieldType(FLD.TYPE)
!length = FLD.Size
!Default = FLD.DefaultValue
!nullable = &quot;YES&quot;

If FLD.REQUIRED Then
!nullable = &quot;NO&quot;
End If

.Update
Next
End If
Next

End With

MsgBox &quot;Tables have been documented&quot;, vbInformation, &quot;TABLES DOCUMENTED&quot;

Rs.Close
DB.Close

Exit_Error_DocumentTables:

Set TDF = Nothing
Set Rs = Nothing
Set DB = Nothing

Exit Sub

Error_DocumentTables:

Select Case Err.Number

Case 3376

Resume Next 'Ignore error if table not found
Case 3270 'Property Not Found

Resume Next
Case Else

MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_DocumentTables

End Select

End Sub
Private Function FieldType(v_fldtype As Integer) As String
On Error GoTo Error_FieldType

Select Case v_fldtype
Case dbBoolean
FieldType = &quot;Boolean&quot;
Case dbByte
FieldType = &quot;Byte&quot;
Case dbInteger
FieldType = &quot;Integer&quot;
Case dbLong
FieldType = &quot;Long&quot;
Case dbCurrency
FieldType = &quot;Currency&quot;
Case dbSingle
FieldType = &quot;Single&quot;
Case dbDouble
FieldType = &quot;Double&quot;
Case dbDate
FieldType = &quot;Date&quot;
Case dbText
FieldType = &quot;Text&quot;
Case dbLongBinary
FieldType = &quot;LongBinary&quot;
Case dbMemo
FieldType = &quot;Memo&quot;
Case dbGUID
FieldType = &quot;GUID&quot;
End Select

Exit_Error_Fieldtype:
Exit Function

Error_FieldType:
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_Fieldtype

End Function
[/tt]


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com