Dim MyCat As ADOX.Catalog
Dim MyTab As ADOX.Table
Dim MyCol As ADOX.Column
Dim MyPro As ADOX.Property
Dim tSh As Worksheet
Dim i As Long
Dim DataName As String, PassStr As String, TableName As String
DataName = "Excel吧" '數(shù)據(jù)庫(kù)名稱
DataName = ThisWorkbook.Path & "\" & DataName & ".mdb"
If Dir(DataName) = "" Then
MsgBox "數(shù)據(jù)庫(kù)：" & DataName & "不存在！"
Exit Sub
End If
PassStr = "" '數(shù)據(jù)庫(kù)密碼
TableName = "數(shù)據(jù)表2" '數(shù)據(jù)表名稱
Set tSh = ThisWorkbook.Worksheets.Add
tSh.Range("A1:D1") = Array("字段名稱", "字段類型", "字段屬性", "屬性值")
Set MyCat = New ADOX.Catalog
MyCat.ActiveConnection = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & _
DataName & ";Jet OLEDB:Database Password=" & MyPass
Set MyTab = New ADOX.Table
Set MyTab = MyCat.Tables(TableName)
i = 2
For Each MyCol In MyTab.Columns
tSh.Cells(i, 1) = MyCol.Name
tSh.Cells(i, 2) = MyCol.Type
For Each MyPro In MyCol.Properties
tSh.Cells(i, 3) = MyPro.Name
tSh.Cells(i, 4) = MyPro.Value
i = i + 1
Next
Next
tSh.Cells.EntireColumn.AutoFit
MsgBox "字段信息讀取完畢!", , "提示"
End Sub
請(qǐng)自己修改程序中的數(shù)據(jù)庫(kù)名，密碼與數(shù)據(jù)表名，路徑為包含程序的Excel文件的目錄中。
從程序的運(yùn)行結(jié)果可以看到，列舉的屬性有：
0 Autoincrement 自動(dòng)編號(hào)
1 Default 默認(rèn)值
2 Description
3 Nullable 必填字段
4 Fixed Length
5 Seed
6 Increment
7 Jet OLEDB:Column Validation Text 有效性文本
8 Jet OLEDB:Column Validation Rule 有效性規(guī)則
9 Jet OLEDB:IISAM Not Last Column
10 Jet OLEDB:AutoGenerate
11 Jet OLEDB:One BLOB per Page
12 Jet OLEDB:Compressed UNICODE Strings
13 Jet OLEDB:Allow Zero Length 允許空字符串
14 Jet OLEDB:Hyperlink 超鏈接型
