' Schema
Dim cn As ADODB.Connection
Dim rsSchema As ADODB.Recordset
Dim fld As ADODB.Field
Dim rCriteria As Variant

Set cn = New ADODB.Connection

With cn
   .Provider = "MSDASQL"   'default Provider=MSDASQL
   .CursorLocation = adUseServer
   .ConnectionString = "driver={SQL Server};server=(local);" & _
                 "uid=sa;pwd=;database=pubs"
   .Open
End With

'Pass in the table name to retrieve index info. The other
'array parameters may be defined as follows:
'    TABLE_CATALOG  (first parameter)
'    TABLE_SCHEMA   (second)
'    INDEX_NAME     (third)
'    TYPE           (fourth)
'    TABLE_NAME     (fifth, e.g. "employee")
rCriteria = Array(Empty, Empty, Empty, Empty, "employee")

Set rsSchema = cn.OpenSchema(adSchemaIndexes, rCriteria)

Debug.Print "Recordcount: " & rsSchema.RecordCount

While Not rsSchema.EOF
      Debug.Print "==================================================="

   For Each fld In rsSchema.Fields
      Debug.Print fld.Name
      Debug.Print fld.Value
      Debug.Print "------------------------------------------------"
   Next
   rsSchema.MoveNext
Wend

rsSchema.Close
Set rsSchema = Nothing
cn.Close
Set cn = Nothing
Set fld = Nothing

'Session pooling
'Global connect handle declaration
Dim conn1 As New ADODB.Connection

Private Sub Form_Load()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer

'Do not uncomment the following. Let time to be default, that is, never 
'expire conn1.CommandTimeout = 60 is not enough to keep connection alive.

    conn1.Open "dsn=pubs;uid=sa;pwd=;"

    For i = 0 To 10
        conn.Open "dsn=pubs;uid=sa;pwd=;"
        rs.Open "select * from authors", conn
        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing
    Next

    conn1.Close
    Set conn1 = Nothing

End Sub 
