ajetrumpet
Banned
- Local time
- Today, 16:35
- Joined
- Jun 22, 2007
- Messages
- 5,638
This was kinda fun to write, so I thought I'd post it here. The function creates a table to display information about user-created tables. It also uses a separate function to convert data type values to readable strings. It is not all-inclusive, but it converts most of the types you will probably work with in your tables. The main function gives you the names, data types, and sizes of every field found in a table:
Conversion Function:
Either one of the attached files demonstrate what the outcome looks like.
Code:
Function GetTableData()
On Error GoTo ErrorHandler
Dim fld As Field
Dim str As String
Dim i As Integer
Dim db As Database
Set db = CurrentDb()
Dim rsNEW As Recordset
Dim rsLOOP As Recordset
Dim tbldef As TableDef
Set tbldef = db.CreateTableDef("DBdata")
With tbldef
.Fields.Append .CreateField("TableName", dbText)
.Fields.Append .CreateField("FieldName", dbText)
.Fields.Append .CreateField("FieldType", dbText)
.Fields.Append .CreateField("FieldSize", dbText)
End With
db.TableDefs.Append tbldef
Set rsNEW = db.OpenRecordset("DBdata")
For Each tbldef In db.TableDefs
If Not tbldef.Name Like "msys*" Then
If Not tbldef.Name Like "~tmp*" Then
If Not tbldef.Name = "DBdata" Then
Set rsLOOP = db.OpenRecordset(tbldef.Name)
For i = 0 To rsLOOP.Fields.Count - 1
Set fld = rsLOOP.Fields(i)
With rsNEW
.AddNew
!tablename = tbldef.Name
!FieldName = fld.Name
!FieldType = FieldType(fld.Type)
!FieldSize = fld.Size
.Update
End With
Next i
rsLOOP.Close
End If
End If
End If
Next tbldef
Set fld = Nothing
Set rsNEW = Nothing
Set rsLOOP = Nothing
ErrorHandler:
Exit Function
End Function
Code:
Public Function FieldType(IntegerType As Integer) As String
Select Case IntegerType
Case dbText
FieldType = "Text"
Case dbMemo
FieldType = "Memo"
Case dbByte
FieldType = "Byte"
Case dbSingle
FieldType = "Single"
Case dbDouble
FieldType = "Double"
Case dbDecimal
FieldType = "Decimal"
Case dbInteger
FieldType = "Integer"
Case dbBoolean
FieldType = "Boolean"
Case dbCurrency
FieldType = "Currency"
Case dbAttachment
FieldType = "Attachment"
Case dbDate
FieldType = "Date / Time"
Case dbLong
FieldType = "Long Integer"
Case Else
FieldType = "Other"
End Select
End Function
Last edited:
