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

Материал из VB Эксперт
Версия от 15:46, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Checking for Existence of Tables

   <source lang="vb">

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

</source>
   
  


Check table definition

   <source lang="vb">

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

</source>
   
  


Get Table documents

   <source lang="vb">

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

</source>
   
  


Listing Table Properties

   <source lang="vb">

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

</source>
   
  


List table types

   <source lang="vb">

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

</source>
   
  


Table documents

   <source lang="vb">

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

</source>