Type Property Example (Field) (VB)


This example demonstrates the Type property by displaying the name of the constant that corresponds to the value of the Type property of all the Field objects in the Employees table. The FieldType function is required for this procedure to run.

Public Sub Main()
    On Error GoTo ErrorHandler

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

    Dim Cnxn As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim strCnxn As String
    Dim strSQLEmployee As String
    Dim FieldType As String

    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn

    ' Open recordset with data from Employees table
    Set rstEmployees = New ADODB.Recordset
    strSQLEmployee = "employee"
    rstEmployees.Open strSQLEmployee, Cnxn, , , adCmdTable
    'rstEmployees.Open strSQLEmployee, Cnxn, adOpenStatic, adLockReadOnly, adCmdTable
     ' the above two lines of code are identical

    Debug.Print "Fields in Employees Table:" & vbCr

    ' Enumerate Fields collection of Employees table
    For Each fld In rstEmployees.Fields
        ' translate field-type code to text
        Select Case fld.Type
            Case adChar
               FieldType = "adChar"
            Case adVarChar
               FieldType = "adVarChar"
            Case adSmallInt
               FieldType = "adSmallInt"
            Case adUnsignedTinyInt
               FieldType = "adUnsignedTinyInt"
            Case adDBTimeStamp
               FieldType = "adDBTimeStamp"
        End Select
        ' show results
        Debug.Print "  Name: " & fld.Name & vbCr & _
          "  Type: " & FieldType & vbCr
    Next fld

    ' clean up
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub

    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing

    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing

    Set fld = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

Attribute VB_Name = "TypeField"