|
查询类型 标准
============================= 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=<servername>;" & _
' "User ID=sa;password=;" End Sub |
185979,186246
|
|
1004413
