Hi RosuAndreiMihai,
Here is a function that will return a count of the tables, the table name,
and the names, type and size of each field. The results are printed to the
Immediate Window, and written to a text file that is in the same folder as
the .mdb file. I'll leave it to you to extend this code for any other desired
properties. Note: I have removed some of the normal indenting, in an effort
to help prevent line wrap in this newsgroup posting. You'll likely want to
add this back in, to help with readability.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
Sub EnumerateTablesAndFieldsAndType()
On Error GoTo ProcError
Dim db As DAO.Database
Dim intLoop As Integer
Dim intloop2 As Integer
Dim intNum As Integer
Set db = CurrentDb()
intNum = FreeFile
'Open Fields.txt" in the current folder for Output
Open CurrentProject.Path & "\Tables and Fields.txt" For Output As intNum
' Print information about TableDefs collection.
Debug.Print db.TableDefs.Count & " TableDefs"
Write #1, db.TableDefs.Count & " TableDefs"
For intLoop = 0 To db.TableDefs.Count - 1
Debug.Print
Write #1, ""
Debug.Print "Table " & intLoop & ": " & db.TableDefs(intLoop).Name
Write #1, "Table " & intLoop & ": " & db.TableDefs(intLoop).Name
For intloop2 = 0 To db.TableDefs(intLoop).Fields.Count - 1
Debug.Print " Field " & intloop2 & ": " _
& db.TableDefs(intLoop).Fields(intloop2).Name _
& ", Type: " &
FieldTypeName(db.TableDefs(intLoop).Fields(intloop2).Type) _
& ", Size: " & db.TableDefs(intLoop).Fields(intloop2).size
Write #1, " Field " & intloop2 & ": " _
& db.TableDefs(intLoop).Fields(intloop2).Name _
& ", Type: " &
FieldTypeName(db.TableDefs(intLoop).Fields(intloop2).Type) _
& ", Size: " & db.TableDefs(intLoop).Fields(intloop2).size
Next intloop2
Next intLoop
ExitProc:
'Cleanup
On Error Resume Next
Close #intNum
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure EnumerateTablesAndFieldsAndType..."
Resume ExitProc
End Sub
Function FieldTypeName(n As Long) As String
'Author: Allen Browne
http://allenbrowne.com/
'Purpose: Converts the numeric results of DAO fieldtype to text.
'Note: fld.Type is Integer, but the constants are Long.
Dim strReturn As String 'Name to return
Select Case n
Case dbBoolean: strReturn = "Yes/No" ' 1
Case dbByte: strReturn = "Byte" ' 2
Case dbInteger: strReturn = "Integer" ' 3
Case dbLong: strReturn = "Long Integer" ' 4
Case dbCurrency: strReturn = "Currency" ' 5
Case dbSingle: strReturn = "Single" ' 6
Case dbDouble: strReturn = "Double" ' 7
Case dbDate: strReturn = "Date/Time" ' 8
Case dbBinary: strReturn = "Binary" '(no interface) 9
Case dbText: strReturn = "Text" ' 10
Case dbLongBinary: strReturn = "OLE Object" ' 11
Case dbMemo: strReturn = "Memo" ' 12
Case dbGUID: strReturn = "GUID" ' 15
'Attached tables only: cannot create these in JET.
Case dbBigInt: strReturn = "Big Integer" ' 16
Case dbVarBinary: strReturn = "VarBinary" ' 17
Case dbChar: strReturn = "Char" ' 18
Case dbNumeric: strReturn = "Numeric" ' 19
Case dbDecimal: strReturn = "Decimal" ' 20
Case dbFloat: strReturn = dbFloat ' 21
Case dbTime: strReturn = "Time" ' 22
Case dbTimeStamp: strReturn = "Time Stamp" ' 23
'Complex data types in Access 2007 and later.
' Case dbAttachment: strReturn = "Attachment" '101
' Case dbComplexByte: strReturn = "Complex byte" '102
' Case dbComplexInteger: strReturn = "Complex Integer" '103
' Case dbComplexLong: strReturn = "Complex Long" '104
' Case dbComplexSingle: strReturn = "Complex Single" '105
' Case dbComplexDouble: strReturn = "Complex Double" '106
' Case dbComplexGUID: strReturn = "Complex GUID" '107
' Case dbComplexDecimal: strReturn = "Complex Decimal" '108
' Case dbComplexText: strReturn = "Complex text" '109
Case Else: strReturn = "Field type " & n & " unknown"
End Select
FieldTypeName = strReturn
End Function
__________________________________________
