[AccessD] Deconstructing Tables

Sub DescribeAllTables()
    Dim db As DAO.Database, tdf As DAO.TableDef
    Dim test As String
    Set db = CurrentDb

        For Each tdf In db.TableDefs
            test = DescribeTbl(db, tdf.Name)
            Debug.Print tdf.Name & " " & test
        Next

    Set db = Nothing
End Sub

Private Function DescribeTbl(db As DAO.Database, tdfName As String) As 
String
    Dim tdf As DAO.TableDef, fld As DAO.Field, strSQL As String
        'Set db = CurrentDb
        Set tdf = db.TableDefs(tdfName)
        If GetTableType(tdf.Attributes) <> "System Table" And 
GetTableType(tdf.Attributes) <> "Hidden(temporary)" Then
            'Debug.Print tdf.Name & ": " & GetTableType(tdf.Attributes)
            For Each fld In tdf.Fields
                strSQL = strSQL & "[" & fld.Name & "] " & 
getPropertyTypeName(fld.Type) & ", "
            Next
            strSQL = Left(strSQL, Len(strSQL) - 2)
        End If
        'Debug.Print strSQL
    DescribeTbl = strSQL
    Set fld = Nothing
    Set tdf = Nothing
End Function

Private Function GetTableType(T As Long)
    Select Case T
        '65536
        Case dbAttachExclusive
            GetTableType = "Linked, Exclusive"
        '131072
        Case dbAttachSavePWD
            GetTableType = "Saves UID,Pwd for linked table."
        '-2147483646 + 2
        Case (dbSystemObject And 2), (dbSystemObject - 2)
            GetTableType = "System Table"
        '2147483646 + 1
        Case (dbHiddenObject And 1)
            GetTableType = "Hidden(temporary)"
        '1073741824
        Case dbAttachedTable
            GetTableType = "Linked, non-ODBC"
        '536870912
        Case dbAttachedODBC
            GetTableType = "Linked, ODBC"
    End Select

End Function

Private Function getPropertyTypeName(T As Integer)
        'Case "Parameter", "Property"
            Select Case T
                Case dbBigInt
                    getPropertyTypeName = "Big Integer"
                Case dbBinary
                    getPropertyTypeName = "Binary"
                Case dbBoolean
                    getPropertyTypeName = "YesNo"
                Case dbByte
                    getPropertyTypeName = "Byte"
                Case dbChar
                    getPropertyTypeName = "Char"
                Case dbCurrency
                    getPropertyTypeName = "Currency"
                Case dbDate
                    getPropertyTypeName = "DateTime"
                Case dbDecimal
                    getPropertyTypeName = "Decimal"
                Case dbDouble
                    getPropertyTypeName = "Double"
                Case dbFloat
                    getPropertyTypeName = "Float"
                Case dbGUID
                    getPropertyTypeName = "Guid"
                Case dbInteger
                    getPropertyTypeName = "Integer"
                Case dbLong
                    getPropertyTypeName = "Long"
                Case dbLongBinary
                    getPropertyTypeName = "Long Binary (OLE Object)"
                Case dbMemo
                    getPropertyTypeName = "Memo"
                Case dbNumeric
                    getPropertyTypeName = "Numeric"
                Case dbSingle
                    getPropertyTypeName = "Single"
                Case dbText
                    getPropertyTypeName = "Text"
                Case dbTime
                    getPropertyTypeName = "Time"
                Case dbTimeStamp
                    getPropertyTypeName = "Time Stamp"
                Case dbVarBinary
                    getPropertyTypeName = "VarBinary"
            End Select
End Function

Michael R. Mattys
MapPoint Developer
www.mattysconsulting.com

----- Original Message ----- 
From: "Bryan Carbonnell" <carbonnb at gmail.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Tuesday, February 28, 2006 7:56 AM
Subject: [AccessD] Deconstructing Tables


> Does anyone know of any tool(s) that will go through the tables in and
> A2K MDB and return the DDL statement to build the table?
>
> What I'm trying to do is do a test port of an application that I have
> written in Access to OpenOffice.org Base and instead of rebuilding the
> tables by hand one by one, I thought I'd try the DDL route.
>
> --
> Bryan Carbonnell - carbonnb at gmail.com
> Life's journey is not to arrive at the grave safely in a well
> preserved body, but rather to skid in sideways, totally worn out,
> shouting "What a great ride!"
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com