[RESOLVED] runtime: determining database table field types

[Muddy]

i want to check each column type of a table to ensure they are certain types before operating on the table. How can I do this?

Thanks in advance for any help!

[Hack]

You didn't say what type of database you are using, so I'm assuming SQL Server. Here is a little snippet....modify as needed.

Private Sub GetDbTables()

   Dim Cnxn As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnxn As String
      
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=xxxx;User Id=xxxx;Password=xxxxxx; "
   Cnxn.Open strCnxn
      
   Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
   Do Until rstSchema.EOF
      List1.AddItem "Table name: " & rstSchema!TABLE_NAME & vbCr & "Table type: " & rstSchema!TABLE_TYPE & vbCr
      rstSchema.MoveNext
   Loop
   
   ' clean up
   rstSchema.Close
   Cnxn.Close
   Set rstSchema = Nothing
   Set Cnxn = Nothing
   
End Sub

[sparbag]

Hello Muddy

Firstly add a references to Microsoft ADO Ext 2.7 for DDL and Security and Microsoft ActiveX Data Objects 2.X Library
Place a label(label1) on a form and copy this code.

Option Explicit
Const DB_PATH = "C:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb"

Private Sub Form_Load()
Dim conn As String
    conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_PATH
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = conn
    cn.Open
    
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strOut As String
Set cat.ActiveConnection = cn
Set tbl = cat.Tables("Titles")
For Each col In tbl.Columns
    strOut = strOut & "Name = " & col.Name & Space$(10)
    strOut = strOut & "Type = " & ColumnType(col) & Space$(10)
    strOut = strOut & "Size = " & col.DefinedSize & vbCrLf
Next col
Label1.Caption = strOut
cn.Close
Set cn = Nothing
End Sub

Function ColumnType(col As ADOX.Column) As String
    On Error GoTo ColumnType_Error
    Select Case col.Type
        Case adBigInt: ColumnType = "adBigInt"
        Case adBinary: ColumnType = "adBinary"
        Case adBoolean: ColumnType = "adBoolean"
        Case adBSTR: ColumnType = "adBSTR"
        Case adChapter: ColumnType = "adChapter"
        Case adChar: ColumnType = "adChar"
        Case adCurrency: ColumnType = "adCurrency"
        Case adDate: ColumnType = "adDate"
        Case adDBDate: ColumnType = "adDBDate"
        Case adDBTime: ColumnType = "adDBTime"
        Case adDBTimeStamp: ColumnType = "adDBTimeStamp"
        Case adDecimal: ColumnType = "adDecimal"
        Case adDouble: ColumnType = "adDouble"
        Case adEmpty: ColumnType = "adEmpty"
        Case adError: ColumnType = "adError"
        Case adFileTime: ColumnType = "adFileTime"
        Case adGUID: ColumnType = "adGUID"
        Case adIDispatch: ColumnType = "adIDispatch"
        Case adInteger: ColumnType = "adInteger"
        Case adIUnknown: ColumnType = "adIUnknown"
        Case adLongVarBinary: ColumnType = "adLongVarBinary"
        Case adLongVarChar: ColumnType = "adLongVarChar"
        Case adLongVarWChar: ColumnType = "adLongVarWChar"
        Case adNumeric: ColumnType = "adNumeric"
        Case adPropVariant: ColumnType = "adPropVariant"
        Case adSingle: ColumnType = "adSingle"
        Case adSmallInt: ColumnType = "adSmallInt"
        Case adTinyInt: ColumnType = "adTinyInt"
        Case adUnsignedBigInt: ColumnType = "adUnsignedBigInt"
        Case adUnsignedInt: ColumnType = "adUnsignedInt"
        Case adUnsignedSmallInt: ColumnType = "adUnsignedSmallInt"
        Case adUnsignedTinyInt: ColumnType = "adUnsignedTinyInt"
        Case adUserDefined: ColumnType = "adUserDefined"
        Case adVarBinary: ColumnType = "adVarBinary"
        Case adVarChar: ColumnType = "adVarChar"
        Case adVariant: ColumnType = "adVariant"
        Case adVarNumeric: ColumnType = "adVarNumeric"
        Case adVarWChar: ColumnType = "adVarWChar"
        Case adWChar: ColumnType = "adWChar"
        Case Else: ColumnType = "<unknown>"
    End Select
    Exit Function
    
ColumnType_Error:
    ColumnType = "<" & Err.Description & ">"
End Function

You may have a column type that causes and error for this just delete the column type or add to the error handler.
Obviously this is for Access as I haven't got a function that names the column types for SQL server but there is room for adaptation.

Kind regards
Steve

[si_the_geek]

Just for info, both of the above methods should work for most database systems - only the connection string (and any field/table names) should need to change.

[Muddy]

This place is great! I would have been happy with just one option! Thanks!!!