獲取access庫中表的個數及表的名稱&#091;收藏&#093;

2024-07-21 02:09:34

字體：大中小

來源：轉載

供稿：網友

'功能：獲取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;

上一篇：ACCESS中使用SQL語句應注意的地方及幾點技巧

下一篇：備份你的改過后綴名的ACCESS數據庫