1----Public Sub 例7_1()
Dim myData As String
Dim myDb As DAO.Database
'指定要打開的數(shù)據(jù)庫名稱
myData = ThisWorkbook.Path & "\NewData.mdb"
'判斷數(shù)據(jù)庫文件是否存在，如果不存在，就彈出警告信息
If Dir(myData) = "" Then
MsgBox "指定的數(shù)據(jù)庫不存在！", vbCritical, "無法打開數(shù)據(jù)庫"
Exit Sub
End If
'打開數(shù)據(jù)庫
Set myDb = OpenDatabase(myData, True, False, ";pwd=H1X2L3")
'顯示打開信息
MsgBox "已經(jīng)成功打開指定的數(shù)據(jù)庫！", vbInformation, "打開數(shù)據(jù)庫"
myDb.Close '關(guān)閉數(shù)據(jù)庫
Set myDb = Nothing '釋放變量
End Sub
2----Public Sub 例7_2()
Dim myDb As DAO.Database
Dim mytbl As DAO.TableDef
mydata = ThisWorkbook.Path & "\客戶管理.mdb" '指定數(shù)據(jù)庫文件
Dim i As Integer
'準備工作表
Cells.Clear
i = 2
Range("A1:B1") = Array("表名稱", "表類型")
'建立與數(shù)據(jù)庫的連接
Set myDb = OpenDatabase(mydata)
'開始獲取表信息
For Each mytbl In myDb.TableDefs
Cells(i, 1) = mytbl.Name '獲取表名稱
Cells(i, 2) = TypeToTable(mytbl.Attributes) '獲取數(shù)據(jù)表類型
i = i + 1
Next mytbl
Cells.Columns.AutoFit
'關(guān)閉數(shù)據(jù)庫
myDb.Close
'釋放變量
Set mytbl = Nothing
Set myDb = Nothing
End Sub
Public Function TypeToTable(myValue As Long) As String
Select Case myValue
Case 2: TypeToTable = "ACCESS TABLE"
Case -2147483648#: TypeToTable = "SYSTEM TABLE"
Case 0: TypeToTable = "TABLE"
End Select
End Function
3---Public Sub 例7_3()
Dim myDb As DAO.Database
Dim mytbl As DAO.TableDef
mydata = ThisWorkbook.Path & "\客戶管理.mdb" '指定數(shù)據(jù)庫文件
Dim i As Integer
'準備工作表
Cells.Clear
i = 2
Range("A1") = "數(shù)據(jù)表名稱"
'開始獲取數(shù)據(jù)表名稱
Set myDb = OpenDatabase(mydata)
For Each mytbl In myDb.TableDefs
If Left(mytbl.Name, 4) <> "MSys" Then
'或者使用語句
'If mytbl.Attributes = 0 Then
Cells(i, 1) = mytbl.Name '獲取數(shù)據(jù)表名稱
i = i + 1
End If
Next mytbl
Cells.Columns.AutoFit
'關(guān)閉數(shù)據(jù)庫
myDb.Close
'釋放變量
Set mytbl = Nothing
Set myDb = Nothing
End Sub
