用能ADO怎么得到数据库(如ACCESS)中的所有表名?'---------------------------------------------------------------------------- ' 'Author:lihonggen0 'Date:2003-6-19 '功能:获取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 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所有的表可以﹐但所有的列不知是什么得的﹖sql server的表可以找得到
转自:http://www.access911.net/77FAB71E14DC.htm使用 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
'
'Author:lihonggen0
'Date:2003-6-19
'功能:获取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 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所有的表可以﹐但所有的列不知是什么得的﹖sql server的表可以找得到
在 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