原文:http://access911.net/77FAB71E14DC.htm
查询类型 标准 =============================
adSchemaTables TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE |
使用 adSchemaTables 列出数据库中所有的表 在 Microsoft Access 97 and Access 2000 中以下例子展示了如何列出northwind数据库中所有的表和查询
Set rs = cn.OpenSchema(adSchemaTables) While Not rs.EOF Debug.Print rs!TABLE_NAME rs.MoveNext Wend |
只列出表用以下的代码:
Set rs = cn.OpenSchema(adSchemaTables, _ Array(Empty, Empty, Empty, "Table") |
在 Microsoft SQL Server 6.5 and 7.0 中以下代码列出Publs中所有的表和视图
Set rs = cn.OpenSchema(adSchemaTables) |
只列出所有表用:
Set rs = cn.OpenSchema(adSchemaTables, _ Array("Pubs", Empty, Empty, "Table") |
查询类型 标准字 ===============================
adSchemaColumns TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME | 用 adSchemaColumns 列出表中所有字段 在 Microsoft Access 97 and Access 2000 中列出 northwind.mdb 数据库 employees 表的所有字段代码如下:
Set rs = cn.OpenSchema(adSchemaColumns,Array(Empty, Empty, "Employees")
While Not rs.EOF Debug.Print rs!COLUMN_NAME rs.MoveNext Wend |
注意:需要OLE DB Provider for ODBC 和 Jet ODBC Driver 和 Jet OLE DB Providers支持
在 Microsoft SQL Server 6.5 and 7.0 中列出 Pubs database 中 Authors 表的所有字段用以下代码:
Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", "Authors") |
查询类型 标准字 ================================
adSchemaIndexes TABLE_CATALOG TABLE_SCHEMA INDEX_NAME TYPE TABLE_NAME |
在下面例子中你必须提供一个索引名让 adSchemaIndexes querytype 使用 在 Microsoft Access 97 and Access 2000 中列出 northwind.mdb 数据库的 employees 表的所有索引用以下代码:
Set rs = cn.OpenSchema(adSchemaIndexes, _ Array(Empty, Empty, Empty, Empty, "Employees")
While Not rs.EOF Debug.Print rs!INDEX_NAME rs.MoveNext Wend |
在 Microsoft SQL Server 6.5 and 7.0 中列出 Pusb 数据库 Authors 表的所有索引用以下代码:
Set rs = cn.OpenSchema(adSchemaIndexes, _ Array("Pubs", "dbo", Empty, Empty, "Authors") |
下面是一段完整的代码展示如何在 Sql Server + VB 中使用:
'Open the proper connection. Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset
Private Sub Command1_Click() 'Getting the information about the columns in a particular table. Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", _ "authors")) While Not rs.EOF Debug.Print rs!COLUMN_NAME rs.MoveNext Wend
End Sub
Private Sub Command2_Click() 'Getting the information about the primary key for a table. Set rs = cn.OpenSchema(adSchemaPrimaryKeys, Array("pubs", "dbo", _ "authors")) MsgBox rs!COLUMN_NAME End Sub
Private Sub Command3_Click() 'Getting the information about all the tables. Dim criteria(3) As Variant criteria(0) = "pubs" criteria(1) = Empty criteria(2) = Empty criteria(3) = "table" Set rs = cn.OpenSchema(adSchemaTables, criteria) While Not rs.EOF Debug.Print rs!TABLE_NAME
rs.MoveNext Wend
End Sub
Private Sub Form_Load() cn.Open "dsn=pubs;uid=sa;pwd=;" 'To test with the Native Provider for SQL Server, comment the ' line above then uncomment the following line. Modify to use ' your server. 'cn.Open "Provider=SQLOLEDB;Data Source=;" & _ ' "User ID=sa;password=;"
End Sub |
|