I have searched a great deal and have not found a solution.
I seek some vba code that would allow me to insert table, field, and field property information from the msysobjects table into my own "Data Dictionary" table.
I am trying to document/organize a mangled database "implemented" by a number of people over the years and need to get everything into a table.
I have used the documenter, but can only output to word or excel. I need the info in an Access table.
Have you considered exporting to Excel and then just turning around and importing back into Access from Excel? You may have to reformat the Excel file a little, but I can't think of a reason that wouldn't work.
Perfect... except that the cost is $299. I can't get my company to pay for it... I already went down that road.
I know the msysobjects table contains the table names... I can loop through that with no problem. How do I reference the fields in each table, then loop through a query (to count records) for each field in each table, saving the results to a table?
I seek some vba code that would allow me to insert table, field, and field property information from the msysobjects table into my own "Data Dictionary" table.
The following function is kind of an extension of what I wrote for someone else in post #5 here. But anyway, if I was going to try to do what you want, I would probably use the SELECT CASE statement in the conversion function I had to call, just like I did in the other thread. I would write a CASE statement for each field type that you could choose in a field (depending on your version of Access). I don't think you have to do it this way, which is pretty manual, but any information from the VB libraries that I (think) you can use to consolidate this is probably beyond my capabilities. Regardless though, here is an example of what I would write if I already had a table set up (named "DBdata") with fields named "TableName", "FieldName", and "FieldProperty":
Code:
Function GetDBdata()
Dim db As Database
Set db = CurrentDb()
Dim tbldef As TableDef
Dim fld As Field
Dim i As Integer
Dim rsOLD As Recordset
Dim rsNEW As Recordset
Dim str As String
Set rsNEW = db.OpenRecordset("DBdata")
For Each tbldef In db.TableDefs
str = tbldef.Name
If Not tbldef.Name Like "msys*" Then
[COLOR="Red"]If Not tbldef.Name Like "~tmp*" Then[/COLOR]
Set rsOLD = db.OpenRecordset(str)
For i = 0 To rsOLD.Fields.Count - 1
Set fld = rsOLD.Fields(i)
with rsNEW
.AddNew
!tablename = str
!fieldname = fld.Name
!FieldProperty = [B][COLOR="Red"][U]FieldType(fld.Type)[/U][/COLOR][/B]
.Update
end with
Next i
Set fld = Nothing
rsOLD.Close
Set rsOLD = Nothing
End If
End If
Next
Set rsNEW = Nothing
Set rsOLD = Nothing
End Function
The above code would use something like the following function to convert the field data type into a readable string:
Code:
Public Function FieldType(IntegerType As Integer) As String
Select Case IntegerType
Case dbtext
FieldType = "Text"
Case dbmemo
FieldType = "Memo"
Case dbdate
FieldType = "Date / Time"
Case dbcurrency
FieldType = "Currency"
Case dbboolean
FieldType = "Boolean"
End Select
End Function
The line in red is obviously optional, but I put it there because I've recently run into annoying issues regarding these tables. The underlined code is the called function. This is not a comprehensive fix by any means, because there are many different field types that could be involved with all of your tables, but maybe it can give you an idea of what you can do to get what you want.
I had to tweak a little (rs.Fields.Count - 1 should be rsOLD.Fields.Count -1) and I had to add some IntegerTypes, but other that that it worked like a charm.
I'm not perfect, as you have seen. 
