[RESOLVED] runtime: determining database table field types

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!

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.

Code:

---

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

---

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.

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

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.

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