ms Access

How do i get the data type,data size and other details of a column in a table ?
I am using MS access as db.

Hi

You can use this function and can customize this as per your requirement. If you run this function in debug mode, you can see all the related properties of table and its fields.

Function TableDef()
Dim dbs As Database
Dim tdf As TableDef, Fld As Field
Dim prop As Property


Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
MsgBox "Table"
If left(tdf.Name, 4) <> "MSys" Then
For Each prop In tdf.Properties
MsgBox " " & prop.Name & " = " & prop.Value
Next prop

MsgBox "Fields"
For Each Fld In tdf.Fields
For Each prop In Fld.Properties
On Error Resume Next
MsgBox " " & prop.Name & " = " & IIf(prop = "", "[empty]", prop.Value)
On Error GoTo 0
Next prop
Next Fld
End If
Next tdf
dbs.Close
Exit Function
End Function

You can use the following SQL command:

DESCRIBE name_of_your_table;

You can use the field properties to get the information you are looking for. Here is some example code which you can use to gather info on your data columns in a table.

<From an new Exe project, (1) add a command button, and (2) select the DAO object library -3.51 or 3.6- from the menu selection for references under Project>


Const sDATASOURCE = "Nwind.mdb"
private db as Database
private rc as Recordset
private Sub Command1_Click()
Dim sSQL as string
Dim dField as Field

set db = DBEngine.OpenDatabase(sDATASOURCE)
sSQL = "Select * from Customers"

set rc = db.OpenRecordset(sSQL, dbOpenSnapshot)

for Each dField In rc.Fields
Debug.print dField.Name
If (dField.Type = dbText) then
Debug.print "Text datatype"
else
Debug.print "other datatype"
End If
Debug.print dField.Size
Debug.print dField.Value
next dField

set rc = nothing
set db = nothing
End Sub

You can customised these codes as required. It will display Properties of all the tables including corresponding fields.

Function TableDef() As Boolean
Dim dbs As Database
Dim tdf As TableDef, Fld As Field
Dim prop As Property


Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
MsgBox "Table"
If Left(tdf.Name, 4) <> "MSys" Then
For Each prop In tdf.Properties
MsgBox " " & prop.Name & " = " & prop.Value
Next prop

MsgBox "Fields"
For Each Fld In tdf.Fields
For Each prop In Fld.Properties
On Error Resume Next
If prop.Type = 1 Then
MsgBox "Yes/No"
ElseIf prop.Type = 2 Then
MsgBox "Byte"
ElseIf prop.Type = 3 Then
MsgBox "Integer"
ElseIf prop.Type = 4 Then
MsgBox "Long"
ElseIf prop.Type = 5 Then
MsgBox "Currency"
ElseIf prop.Type = 6 Then
MsgBox "Single"
ElseIf prop.Type = 7 Then
MsgBox "Double"
ElseIf prop.Type = 8 Then
MsgBox "Date/ Time"
ElseIf prop.Type = 10 Then
MsgBox "Text"
ElseIf prop.Type = 11 Then
MsgBox "OLE Object"
ElseIf prop.Type = 12 Then
MsgBox "Memo"
ElseIf prop.Type = 12 Then
MsgBox "Hyperlink"
ElseIf prop.Type = 15 Then
MsgBox "ReplicationId"
End If
MsgBox " " & prop.Name & " = " & IIf(prop = "", "[empty]", prop.Value)
On Error GoTo 0
Next prop
Next Fld
End If
Next tdf
dbs.Close
Exit Function
End Function