asp 獲取access系統(tǒng)表，查詢等操作代碼
2020-07-27 13:06:17
供稿：網(wǎng)友
方法一：直接用ado的OpenSchema()方法打開(kāi)adSchemaTables，建立一個(gè)schema記錄集
Code:
<%
Const adSchemaTables = 20
adSchemaColumns = 4
dim Conn,db
dim ConnStr
db="temp.mdb" 'ACCESS數(shù)據(jù)庫(kù)的文件名，請(qǐng)使用相對(duì)于網(wǎng)站根目錄的的絕對(duì)路徑
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(db)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstr
Dim rstSchema
Dim I
Set rstSchema = Conn.OpenSchema(adSchemaTables)
Do while not rstSchema.EOF
response.write("Table name: "& rstSchema("TABLE_NAME") & vbCr & _
"Table type: " & rstSchema("TABLE_TYPE") & vbCr)
response.write("<br/>")
I = I + 1
rstSchema.MoveNext
Loop
rstSchema.Close
conn.Close
%>
方法二：Access 系統(tǒng)表 MsysObjects 包含了數(shù)據(jù)庫(kù)對(duì)象列表。盡管未在文檔中記載，你仍可通過(guò)查詢它來(lái)獲取你想要的。但是默認(rèn)情況下無(wú)法操作系統(tǒng)表，必須手動(dòng)設(shè)定權(quán)限收才能查詢系統(tǒng)表。
請(qǐng)參考以下動(dòng)畫：如何獲取對(duì)msysobject的操作權(quán)限 http://blog.iyi.cn/user/david/archives/IMAGES/msobject.swf
但是我在access2003中找不到這幾個(gè)系統(tǒng)表，不知道是不是m$增強(qiáng)了access的安全性。
使用下列 SQL 語(yǔ)句來(lái)獲取你想要的
Code:
查詢:
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;
報(bào)表:
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;
