open up a schema with a provider to look at all the database objects : ADODB.Connection « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. ADODB.Connection

open up a schema with a provider to look at all the database objects

 
Sub ReadSchema()
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set conn = New ADODB.Connection
    'Jet
    'With conn
    '    .Provider = "Microsoft.Jet.OLEDB.4.0"
    '    .ConnectionString = "Data source=" & App.Path & "\sample.mdb"
    '    .Open
    'End With
    'SQL Server
    With conn
        .Provider = "SQLOLEDB"
        .ConnectionString = "data source=batman;user id=sa;initial catalog=Northwind"
        .Open
    End With
    Set rst = conn.OpenSchema(adSchemaTables)
    Do Until rst.EOF
        Debug.Print "Catalog: " & rst!Table_Catalog
        Debug.Print "Schema: " & rst!Table_Schema
        Debug.Print "Table Name: " & rst!Table_Name
        Debug.Print "Type: " & rst!Table_Type
        Debug.Print "Date Created: " & rst!Date_Created
        Debug.Print "Date Modified" & rst!Date_Modified
        Debug.Print
        rst.MoveNext
    Loop
    rst.Close
    conn.Close
    
    Set rst = Nothing
    Set conn = Nothing

End Sub

 

Related examples in the same category