请详细参阅ADO参考文档中OpenSchema 如: '功能:获取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;
还是不行,在改用select name from sysobjects where xtype='u';居然说我不存在sysobjects这个表 我头晕 救命啊 我用的office2000,工具-》安全->用户与组的权限->里也没有看到Msysobjects或sysobjects之类的表,只有我自己作的一张用户数据表 这到底是为什么?
如:
'功能:获取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;
http://community.csdn.net/Expert/topic/3460/3460097.xml?temp=.3330652
到底是为什么我在access里的SQL查询里用SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; 能得到表名,而到asp.net里却不行呢!大侠看一下!<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
void Page_Load(object sender,System.EventArgs e)
{
string strConnection="Provider=Microsoft.Jet.OleDb.4.0;";
strConnection+=@"Data Source=d:\MyProgram\score.mdb";
string strSQL="SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;";
OleDbConnection objConnection=new OleDbConnection(strConnection);
OleDbCommand objCommand=new OleDbCommand(strSQL,objConnection);
objConnection.Open();
dgEmps.DataSource=objCommand.ExecuteReader(CommandBehavior.CloseConnection);
dgEmps.DataBind();
}</script>
<html>
<body>
<h2>我真是郁闷!</h2>
<asp:datagrid id="dgEmps" runat="server" CellPadding="3" Font-Name="arial" Font-Size="8pt" HeaderStyle-BackColor="#dcdcdc" HeaderStyle-ForeColor="blue"/>
</body>
</html>
它硬说俺
"不能读取记录;在 'MsysObjects' 上没有读取数据权限"(引用出错信息中的原文)
我头晕
救命啊
我用的office2000,工具-》安全->用户与组的权限->里也没有看到Msysobjects或sysobjects之类的表,只有我自己作的一张用户数据表
这到底是为什么?