获取access库中表的个数及表的名称[收藏]-数据库专栏,MSAccess

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