获取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数据库