方法1:在查询中获取表、查询、窗体、宏、报表、模块的个数和名称
Access 系统表 MsysObjects 包含了数据库对象列表。尽管未在文档中记载,你仍可通过查询它来获取你想要的。但是默认情况下无法操作系统表,必须手动设定权限收才能查询系统表。
警告:不要修改任何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;
举例:
1、先建一个查询,
2、进入设计视图
3、进入SQL视图后。将“Select MSysObjects.Name FROM MsysObjects Where (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 orDER BY MSysObjects.Name;
”放入。如下图
然后,保存一下,退入设计视图,则可见下图:
再保存一下后,退入查询,就可以看到以下结果,见下图:
看到表的名称和总数了吗?再来看看,这与数据库的表有没有误差?见下图:
我数了一下,正是33个表,而且表名一点不差,效果如何?,再看方法2。
方法2:用VB自动获取表的个数和名称,代码如下:
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
同样举一个实例(共有14个表),具体操作演示如下:
1、建个窗体,将代码放入,如图所示: