擷取access庫中表的個數及表的名稱[收藏]

'功能：擷取access庫中表的個數及表的名稱
'用ado怎樣實現
'工程--->引用--->Microsoft ActiveX Data Object 2.x（版本號碼）
'----------------------------------------------------------------------------
Private Sub Form_Load()
Dim adoCN   As New ADODB.Connection                '定義資料庫的串連
Dim strCnn   As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
   str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/Northwind.MDB;Persist Security Info=False"
   adoCN.Open str1
        
   Set rstSchema = adoCN.OpenSchema(adSchemaTables)
    
   Do Until rstSchema.EOF
        If rstSchema!TABLE_TYPE = "TABLE" Then
           out = out & "Table  name:  " & _
               rstSchema!TABLE_NAME & vbCr & _
               "Table  type:  " & rstSchema!TABLE_TYPE & vbCr
            I = I + 1
        End If
        rstSchema.MoveNext
   Loop
   MsgBox I
   rstSchema.Close
    
   adoCN.Close
Debug.Print out
End Sub  

方法二：

 

 Access 系統資料表 MsysObjects 包含了資料庫物件列表。儘管未在文檔中記載，你仍可通過查詢它來擷取你想要的。但是預設情況下無法作業系統表，必須手動設定許可權收才能查詢系統資料表。
請參考以下動畫：如何擷取對msysobject的操作許可權
http://access911.net/eg/swf/msobject.swf

警告：不要修改任何ACCESS系統資料表，否則會出現不可意料的情況.

使用下列 SQL 陳述式來擷取你想要的

查詢:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;

表單:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

報表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

模組:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;

宏:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;