Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String Set cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnn Set rstSchema = cnn1.OpenSchema(adSchemaTables) Do Until rstSchema.EOF Debug.Print "Table name: " & _ rstSchema!TABLE_NAME & vbCr & _ "Table type: " & rstSchema!TABLE_TYPE & vbCr rstSchema.MoveNext Loop rstSchema.Close cnn1.Close
'***** ' DAO: '*****Public Function NonSystemTables(dbPath As String) As Collection'Input: Full Path to an Access Database'Returns: Collection of the names 'of non-system tables in that database 'or Nothing if there is an error'Requires: a reference to data access 'objects (DAO) in your projectOn Error GoTo ErrHandlerDim td As DAO.TableDef Dim db As DAO.Database Dim colTables As CollectionSet db = workspaces(0).opendatabase(dbPath)Set colTables = New Collection For Each td In db.TableDefs If td.Attributes >= 0 And td.Attributes <> dbHiddenObject _ And td.Attributes <> 2 Then
colTables.Add td.Name End If Next db.close Set NonSystemTables = colTablesExit Function ErrHandler: On Error Resume Next If Not db Is Nothing Then db.CloseSet NonSystemTables = NothingEnd Function'***** ' ADO: '***** Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String
Set cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnn
ADOXDim Conn As New ADODB.Connection Dim Cat As New ADOX.Catalog Dim Tbl As ADOX.Table Conn.Open "Provider=SQLOLEDB.1;Password=handsomge;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=ZT\HANDSOMGE" Set Cat.ActiveConnection = Conn PrgBar.Max = Cat.Tables.Count PrgBar.Visible = True
TV1.Nodes.Add , , "Root", "所有表", 1 For Each Tbl In Cat.Tables '便利表 TV1.Nodes.Add "Root", 4, Tbl.Name, Tbl.Name, 2 Dim clm As ADOX.Column
For Each clm In Tbl.Columns TV1.Nodes.Add Tbl.Name, 4, Tbl.Name & clm.Name, clm.Name, 3 Next PrgBar.Value = PrgBar.Value + 1 Next TV1.Nodes.Add , , "views", "所有视图" ' Dim a As View ' For Each a In Cat.Views ' TV1.Nodes.Add "views", 4, a.Name ' Next TV1.Nodes("views").Expanded = True TV1.Nodes.Add , , "Qurreys", "所有查询" PrgBar.Visible = False Conn.Close Set Conn = Nothing Set Cat = Nothing
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext Loop
rstSchema.Close
cnn1.Close
' DAO:
'*****Public Function NonSystemTables(dbPath As String) As Collection'Input: Full Path to an Access Database'Returns: Collection of the names
'of non-system tables in that database
'or Nothing if there is an error'Requires: a reference to data access
'objects (DAO) in your projectOn Error GoTo ErrHandlerDim td As DAO.TableDef
Dim db As DAO.Database
Dim colTables As CollectionSet db = workspaces(0).opendatabase(dbPath)Set colTables = New Collection For Each td In db.TableDefs If td.Attributes >= 0 And td.Attributes <> dbHiddenObject _
And td.Attributes <> 2 Then
colTables.Add td.Name
End If
Next
db.close
Set NonSystemTables = colTablesExit Function
ErrHandler:
On Error Resume Next
If Not db Is Nothing Then db.CloseSet NonSystemTables = NothingEnd Function'*****
' ADO:
'*****
Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub
从提供者获取数据库模式信息。语法Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)返回值返回包含模式信息的 Recordset 对象。Recordset 将以只读、静态游标打开。参数QueryType 所要运行的模式查询类型,可以为下列任意常量。Criteria 可选。每个 QueryType 选项的查询限制条件数组,如下所列:QueryType 值 Criteria 值
AdSchemaAsserts CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCatalogs CATALOG_NAME
AdSchemaCharacterSets CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
AdSchemaCheckConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
AdSchemaCollations COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
AdSchemaColumnDomainUsage DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
AdSchemaColumnPrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
adSchemaColumns TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintTableUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaForeignKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaIndexes TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
adSchemaKeyColumnUsage CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaPrimaryKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMTER_NAME
adSchemaProcedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProviderSpecific 参见说明
adSchemaProviderTypes DATA_TYPE
BEST_MATCH
adSchemaReferentialConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages <无>
adSchemaStatistics TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
GRANTOR
GRANTEE
adSchemaTables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
adSchemaTranslations TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaUsagePrivileges OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
adSchemaViewColumnUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViewTableUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
SchemaID OLE DB 规范没有定义用于提供者模式查询的 GUID。如果 QueryType 设置为 adSchemaProviderSpecific,则需要该参数,否则不使用它。说明OpenSchema 方法返回与数据源有关的信息,例如关于服务器上的表以及表中的列等信息。Criteria 参数是可用于限制模式查询结果的值数组。每个模式查询有它支持的不同参数集。实际模式由 IDBSchemaRowset 接口下的 OLE DB 规范定义。ADO 中所支持的参数集已在上面列出。如果提供者定义未在上面列出的非标准模式查询,则常量 adSchemaProviderSpecific 将用于 QueryType 参数。在使用该常量时需要 SchemaID 参数传递模式查询的 GUID 以用于执行。如果 QueryType 设置为 adSchemaProviderSpecific 但是没有提供 SchemaID,将导致错误。提供者不需要支持所有的 OLE DB 标准模式查询,只有 adSchemaTables、adSchemaColumns 和 adSchemaProviderTypes 是 OLE DB 规范需要的。但是对于这些模式查询,提供者不需要支持上面列出的 Criteria 条件约束。远程数据服务用法 OpenSchema 方法在客户端 Connection 对象上无效。注意 在 Visual Basic 中,在由 Connection 对象的 OpenSchema 方法所返回的 Recordset 中有 4 字节无符号整型 (DBTYPE UI4) 的列无法与其他变量比较。有关 OLE DB 数据类型的详细信息,请参阅“Microsoft OLE DB 程序员参考”的第十章和附录 A。!!!!!!
Dim Cat As New ADOX.Catalog
Dim Tbl As ADOX.Table
Conn.Open "Provider=SQLOLEDB.1;Password=handsomge;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=ZT\HANDSOMGE" Set Cat.ActiveConnection = Conn
PrgBar.Max = Cat.Tables.Count
PrgBar.Visible = True
TV1.Nodes.Add , , "Root", "所有表", 1
For Each Tbl In Cat.Tables '便利表
TV1.Nodes.Add "Root", 4, Tbl.Name, Tbl.Name, 2
Dim clm As ADOX.Column
For Each clm In Tbl.Columns
TV1.Nodes.Add Tbl.Name, 4, Tbl.Name & clm.Name, clm.Name, 3
Next
PrgBar.Value = PrgBar.Value + 1
Next
TV1.Nodes.Add , , "views", "所有视图"
' Dim a As View
' For Each a In Cat.Views
' TV1.Nodes.Add "views", 4, a.Name
' Next
TV1.Nodes("views").Expanded = True
TV1.Nodes.Add , , "Qurreys", "所有查询"
PrgBar.Visible = False
Conn.Close
Set Conn = Nothing
Set Cat = Nothing