asp 擷取access系統資料表，查詢等作業碼

方法一：直接用ado的OpenSchema()方法開啟adSchemaTables，建立一個schema記錄集

Code:
<%
Const adSchemaTables = 20
adSchemaColumns = 4
dim Conn,db
dim ConnStr
db="temp.mdb" 'ACCESS資料庫的檔案名稱，請使用相對於網站根目錄的的絕對路徑
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 系統資料表 MsysObjects 包含了資料庫物件列表。儘管未在文檔中記載，你仍可通過查詢它來擷取你想要的。但是預設情況下無法作業系統表，必須手動設定許可權收才能查詢系統資料表。
請參考以下動畫：如何擷取對msysobject的操作許可權 http://blog.iyi.cn/user/david/archives/IMAGES/msobject.swf

但是我在access2003中找不到這幾個系統資料表，不知道是不是m$增強了access的安全性。

使用下列 SQL 陳述式來擷取你想要的

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;

報表:
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;