VBA/Excel/Access/Word/Access/Table Properties

Checking for Existence of Tables

 
Function TableExists(WhichTable)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    TableExists = False
    MyConn = "C:\t.mdb"
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    Set rst = cnn.OpenSchema(adSchemaTables)
    Do Until rst.EOF
        If LCase(rst!Table_Name) = LCase(WhichTable) Then
            TableExists = True
            GoTo ExitMe
        End If
        rst.MoveNext
    Loop
ExitMe:
    rst.Close
    Set rst = Nothing
    cnn.Close
End Function

Check table definition

 
Sub exaCheckTableDefs()
    Dim db As Database
    Dim tbl As TableDef
    Set db = CurrentDb
    
    Debug.Print db.TableDefs.Count
    
    For Each tbl In db.TableDefs
        Debug.Print tbl.Name & vbTab & TypeName(tbl)
    Next
End Sub

Get Table documents

 
Sub exaTablesDocuments()
 
    Dim db As Database
    Set db = CurrentDb
    Dim docs As Documents
    Dim doc As Document
     
    Set docs = db.Containers!Tables.Documents
    Debug.Print "Count: " & docs.Count
     
    For Each doc In docs
       Debug.Print "Container: " & doc.Container
       Debug.Print "DateCreated: " & doc.DateCreated
       Debug.Print "LastUpdated: " & doc.LastUpdated
       Debug.Print "Name: " & doc.Name
       Debug.Print "Owner: " & doc.Owner
       Debug.Print
    Next doc
End Sub

Listing Table Properties

 
Sub List_TableProperties()
   Dim cat As ADOX.Catalog
   Dim myTable As ADOX.Table
   Dim pr As ADOX.Property
   Set cat = New ADOX.Catalog
   Set cat.ActiveConnection = CurrentProject.Connection
   Set myTable = cat.Tables("vbexTable")
   " retrieve table properties
   For Each pr In myTable.Properties
      Debug.Print myTable.Name & ": " & _
          pr.Name & "= "; pr.Value
   Next
   Set cat = Nothing
End Sub

List table types

 
Sub ListTables()
    
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\mydb.mdb;"
    
    For Each tbl In cat.Tables
       Debug.Print tbl.Name, tbl.Type
    Next
End Sub

Table documents

 
Sub exaTablesDocuments()
    Dim db As Database
    Set db = CurrentDb
    Dim docs As Documents
    Dim doc As Document
    
    Set docs = db.Containers!Tables.Documents
    Debug.Print docs.Count
    For Each doc In docs
        Debug.Print doc.Container
        Debug.Print doc.DateCreated
        Debug.Print doc.LastUpdated
        Debug.Print doc.Name
        Debug.Print doc.Owner
    Next doc
End Sub