Convert ADO numeric Column Type to correct DefinedSize. 1

[JonMa]

When I use the sql SELECT * FROM TABLE i get the correct Name, Type and DefinedSize from the recordset.
But I do not want to use sql but query the catalog:

Dim Conn As New ADODB.Connection
'Open connection you want To get database objects
Conn.Provider = "MSDASQL"
Conn.Open "DSN=...;Database=...;", "UID", "PWD"
'Create catalog object
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveConnection = Conn
'List tables And columns
Dim Table As ADOX.Table, Column As ADOX.Column
For Each Table In Catalog.Tables
For Each Column In Table.Columns
Debug.Print Table.Name & ", " & Column.Name & ", " & Column.Type & ", " & Column.DefinedSize
Next
Next

The problem is that the DefinedSize for query catalog is 0 for numeric types.
Does anyone know of a program to convert Column type to correct size ?

 

[PHV]

Have you tried to look at the NumericScale and Precision properties of the Column object ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JonMa]

Hello PHV.
Yes, if it is decimal I think that should also be used in the calculation also.
Hope someone can help me with a program.

 

[PHV]

The Precision property give you the size required for displaying the max/min value of the numeric field, ie 3 for a Byte, 5 for a SmallInt and 10 for an 32bits Integer.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JonMa]

Hello PHV.
Thank you for your valuable answer, now I'm getting close.
One thing I do not understand though, is how to calculate the same as DefinedSize used in the sql.
DefinedSize=2 for SmallInt in the sql.

 

[PHV]

SmallInt is stored in 16bits word, so 2 bytes.
You should get 4 for 32bits integers.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JonMa]

OK PHV.
Thank you for your information.
My problem has been solved by you.
I do not really need to make a program to convert to number of bytes according to type.
It is good enough with NumericScale and Precision.
Thanks.