VBA/Excel/Access/Word/Access/Table Properties
Содержание
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>