how to get he Data Type information of a field.

[musa.biralo]

Hello,

I have a very simple question...
I have a table with fields. Now i want to know whether a field is
holding a Numeric data or a text. How can i do that? I like to go
through each column and store the name and the Data Type.

Any direction or help will be a big help for me...

Thanks
Musa.Biralo

 

[Jerry Whittle]

With something like Access 2003, go to Tools, Analyze, Documenter. Click on
the Tables tab and Select All. In the Options under Include for Fields, you
probably want the middle option selected.

This will create a report that can be saved.

 

[musa.biralo]

Thanks for the quick reply.

I should have mentioned that I am trying to use some SQL statement to
find that info. I want to do this programmatically not manually.

Any further suggestion?

Thanks
Musa.

 

[Jerry Whittle]

Can't do it in Access with SQL statements like with Oracle (VIEW
SYS.DBA_TAB_COLS) or SQL Server. Best you can come up with are the table,
form, report, etc., names with SQL and Access.

 

[Bob Barrows]

There is no sql statement that will provide that information in Jet, the
way there is in SQL Server. You will need to use either:
DAO: the tabledef object - online help should have examples
ADO: the Openschema method - google should provide examples
ADOX: the Table object - google should provide examples

Incidently, these techniques will only tell you what the fields are
designed to contain. They will not tell you anything about the data
itself. So, for example, if you have a Text field and you want to find
out if it contains numeric data, then you will need to resort to a query
using the VBA IsNumeric function, which is flawed, or a custom VBA
function that uses a regex to test the field contents. I don't want to
waste time going into details if that really wasn't what you were
asking. If it was, you should first google the terms I used to
familiarize yourself with them, and then come back here for further
clarification.

 

[musa.biralo]

Thanks Bob for showing me the direction...

I was able to find some example where it shows how to create a table
but not how to read a table or get the field info...

If you have any bookmarks, please let me know.

thanks again.
musa.biralo

 

[Bob Barrows]

Using DAO, this example from online help should be helpful:

This example shows what properties are valid for a Field object
depending on where the Field resides (for example, the Fields collection
of a TableDef, the Fields collection of a QueryDef, and so forth). The
FieldOutput procedure is required for this procedure to run.

Sub FieldX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim fldTableDef As Field
Dim fldQueryDef As Field
Dim fldRecordset As Field
Dim fldRelation As Field
Dim fldIndex As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

' Assign a Field object from different Fields
' collections to object variables.

Set fldTableDef = _
dbsNorthwind.TableDefs(0).Fields(0)
Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)
Set fldRecordset = rstEmployees.Fields(0)
Set fldRelation =dbsNorthwind.Relations(0).Fields(0)
Set fldIndex = _
dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

' Print report.
FieldOutput "TableDef", fldTableDef
FieldOutput "QueryDef", fldQueryDef
FieldOutput "Recordset", fldRecordset
FieldOutput "Relation", fldRelation

FieldOutput "Index", fldIndex

rstEmployees.Close
dbsNorthwind.Close

End Sub

Sub FieldOutput(strTemp As String, fldTemp As Field)
' Report function for FieldX.

Dim prpLoop As Property

Debug.Print "Valid Field properties in " & strTemp

' Enumerate Properties collection of passed Field
' object.
For Each prpLoop In fldTemp.Properties
' Some properties are invalid in certain
' contexts (the Value property in the Fields

' collection of a TableDef for example). Any
' attempt to use an invalid property will
' trigger an error.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " = " & _
prpLoop.Value
On Error GoTo 0
Next prpLoop

End Sub