pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
pa*************@hotmail.com (Pato Secruza) wrote in message news:<21**************************@posting.google. com>... How can I print the database structure, including non default
variables like Requiered, FieldSize, and most important DESCRIPTION. I
want a table like this:
-----------------------------------------------
| Name | Type | Size | Required | Description |
----------------------------------------------- | ... | ... | ... | ... | ... |
Does anyone know how to do this? I can only print Name and Type as
they are predefined variables. Thanks in advance!
Alternatively, how can I copy my variable descriptions from MS Access'
Design View to another program (SPSS or EXCEL or a text document)? MS
Access doesn't allow copying the description fields (only one at a
time and I have 900 variables in my tables!!) and importing into SPSS
does not include the descriptions. All help is more than welcome!
Cheers!
Patricio
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
Okay, then you could output the whole thing to HTML... just specify
the table and use the OutputTo function...