Printing Access Table Structures

[BhujangaII]

I want to be able to print out the structure of two tables, each with about 100 fields, from two different databases, so I can more easily try to identify corresponding columns. This is so I can eventually create a query that appends the data from one table to the other. I can't seem to find any way to print out such a listing. Is there one?
Thanks you.

 

[petrosky]

Hi there,

Thanks for this answer go to Pieter Linden on this thread...

http://bytes.com/groups/ms-access/189610-printing-ms-access-database-structure-asp

Place the following in a module.

Public Sub DocumentTable(ByVal strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset

Set db = CurrentDb

DoCmd.RunSQL "DELETE * FROM ztblDocumentation;", dbFailOnError

'--Open the recordset that the documentation data will go into
Set rs = db.OpenRecordset("ztblDocumentation", dbOpenTable)

'--Open the tabledef that will be documented.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
rs.AddNew
rs.Fields("TableName") = tdf.Name
rs.Fields("FieldName") = fld.Name
rs.Fields("FieldType") = GetFieldType(fld.Type)
rs.Fields("FieldSize") = fld.Size
rs.Fields("Required") = fld.Required
rs.Fields("Description") = GetFieldDescription(fld.Name, tdf.Name)
rs.Update
Next fld

rs.Close
Set rs = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Private Function GetFieldType(ByVal lngType As Long) As String

Select Case lngType
Case dbBoolean
GetFieldType = "Yes/No"
Case dbCurrency
GetFieldType = "Currency"
Case dbDate
GetFieldType = "Date/Time"
Case dbGUID
GetFieldType = "GUID"
Case dbInteger
GetFieldType = "Integer"
Case dbLong
GetFieldType = "Long Integer"
Case dbLongBinary
GetFieldType = "Long Binary"
Case dbMemo
GetFieldType = "Memo"
Case dbNumeric
GetFieldType = "Numeric"
Case dbSingle
GetFieldType = "Single"
Case dbText
GetFieldType = "Text"
Case dbTime
GetFieldType = "Time"
Case dbTimeStamp
GetFieldType = "Date"
Case dbVarBinary
GetFieldType = "VarBinary.... whatever!"
Case Else
GetFieldType = vbNullString
End Select

End Function


Public Function GetFieldDescription(ByVal strField As String, ByVal strTable As String) As String
On Error GoTo ErrHandler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)

GetFieldDescription = fld.Properties("Description")

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

Exit Function

ErrHandler:
'For Each Err In Errors
Err.Clear
'Next Err
GetFieldDescription = ""

End Function

Create a Table to hold your results in called

ztblDocumentation with the following text fields.

TableName
FieldName
FieldType
FieldSize
Required
Description


On a form create a command button like this one:

Private Sub Command46_Click()

Call DocumentTable("yourtablename")

End Sub

There may be an easier way but this does work.

HTH.

Peter.






Remember- It's nice to be important,
but it's important to be nice

 

[BhujangaII]

Thanks for your help. I endountered the following problem though. When the code got to this statement:

Dim db As DAO.Database

it returned an error message "User defined type not defined". Do you know what needs to be added in for it to work? (I'm using 2003)

Thanks

 

[1726]

Have a look at this menu option:

Tools Analyze Documenter

Here you can select the databse Objects that you want to fully document.

Merry Christmas!

 

[dhookom]

The documenter creates a table in an MDT file that you can link. You can use the documenter and then custom report the results as described on this page

http://www.access.hookom.net/documenter/.

Duane
Hook'D on Access
MS Access MVP

 

[BhujangaII]

Thanks for all your help on this. The document Analyzer does what I need it to do for this case. I tried fooling around with the querying in the ACWZUSERT.MDT database. I had to go firectly into that database - I couldn't link any table from it because MDT wasn't an option in my file type drop down. But once I got in there it didn't appear that any of the information from my table was being saved to those tables. I created the crosstab query and it came up but with headings but no data. I also just scrutinized the table manually and nothing that looked related to my table was in there. I could see that the database itself was opening when I ran the documenter, so I don't think the problem is that it's directing it elsewhere. At this point, that is more of an academic curiosity because, I have the info I really needed.
Thanks.

 

[dhookom]

MDT won't show in the file type dropdown but that doesn't mean you can't type in [blue]*.MDT[/blue] into the file name box.

The table gets populated when you run the documenter.

Duane
Hook'D on Access
MS Access MVP