如何通过ADO获得数据库的字段名，字段类型？在线等。。。

名： recordset.fields(index).name
类型：recordset.fields(index).type
类型是用数字表示的，msdn上有对应表

OpenSchema 方法
从提供者获取数据库模式信息。语法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。

OpenSchema 方法范例
该范例使用 OpenSchema 方法显示 Pubs 数据库内每个表的名称和类型。Public Sub OpenSchemaX()   Dim cnn1 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String

   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   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该范例在 OpenSchema 方法的 Criteria 参数中指定 TABLE_TYPE 查询约束。因此，只返回在 Pubs 数据库中指定视图的模式信息。然后该范例显示每个表的名称和类型。Public Sub OpenSchemaX2()   Dim cnn2 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String

   Set cnn2 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn2.Open strCnn

   Set rstSchema = cnn2.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))      Do Until rstSchema.EOF
         Debug.Print "Table name: " & _
            rstSchema!TABLE_NAME & vbCr & _
            "Table type: " & rstSchema!TABLE_TYPE & vbCr
         rstSchema.MoveNext
      Loop
   rstSchema.Close

   cnn2.Close

End Sub

ado的recordset 对象在返回记录集时，同时也有各字段的名字和类型。
具体是：recordset.Fields.Item(i).Name
        recordset.Fields.Item(i).Type
        类型是枚举型，你可以查对应的值
还有其它属性：
        recordset.Fields.Item(i).DefinedSize   字段的定义宽度
        recordset.Fields.Item(i).ActualSize    字段的实际宽度....

其他我不想再说了，看我用过的：
for i=0 to rs.recordcount-1
print rs.Fields(i).Name & " " & rs.Fields(i).Type "(" & rs.Fields(i).ActualSize & ")"
next

Private Sub Command1_Click()
Dim Cn As New ADODB.Connection
Dim Rs_Table As New ADODB.Recordset
Dim Rs_Colums As New ADODB.Recordset    With Cn   '定义连接
        .CursorLocation = adUseClient
        .Provider = "sqloledb"
        .Properties("Data Source").Value = "LIHG"
        .Properties("Initial Catalog").Value = "NorthWind"
        .Properties("User ID") = "sa"
        .Properties("Password") = "sa"
        .Properties("prompt") = adPromptNever
        .ConnectionTimeout = 15
        .Open

        If .State = adStateOpen Then
            Rs_Table.CursorLocation = adUseClient   '得到所有表名
            Rs_Table.Open "SELECT name From sysobjects WHERE xtype = 'u'", Cn, adOpenDynamic, adLockReadOnly
            Rs_Table.MoveFirst
            Do While Not Rs_Table.EOF
                Debug.Print Rs_Table.Fields("name")
                Rs_Colums.CursorLocation = adUseClient
                Rs_Colums.Open "select * from [" & Rs_Table.Fields("name") & "]", Cn, adOpenStatic, adLockReadOnly
                For I = 0 To Rs_Colums.Fields.Count - 1   ' 循环所有列
                    Debug.Print Rs_Colums.Fields(I).Name   '字段名
                    Debug.Print FieldType(Rs_Colums.Fields(I).Type)  '字段类型
                    Debug.Print Rs_Colums.Fields(I).DefinedSize  '宽度
                Next
                Rs_Colums.Close
                Rs_Table.MoveNext
            Loop
            Rs_Table.Close
            Set Rs_Colums = Nothing
            Set Rs_Table = Nothing

        Else
            MsgBox "数据库连接失败，请找系统管理员进行检查！", 16, cProgramName
            End
        End If
    End With
End Sub'*********************************************************
'* 名称：FieldType
'* 功能：返回字段类型
'* 用法：FieldType(nType as integer)
'*********************************************************
Function FieldType(nType As Integer) As String
    Select Case nType
        Case 128
            FieldType = "BINARY"
        Case 11
            FieldType = "BIT"
        Case 129
            FieldType = "CHAR"
        Case 135
            FieldType = "DATETIME"
        Case 131
            FieldType = "DECIMAL"
        Case 5
            FieldType = "FLOAT"
        Case 205
            FieldType = "IMAGE"
        Case 3
            FieldType = "INT"
        Case 6
            FieldType = "MONEY"
        Case 130
            FieldType = "NCHAR"
        Case 203
            FieldType = "NTEXT"
        Case 131
            FieldType = "NUMERIC"
        Case 202
            FieldType = "NVARCHAR"
        Case 4
            FieldType = "REAL"
        Case 135
            FieldType = "SMALLDATETIME"
        Case 2
            FieldType = "SMALLMONEY"
        Case 6
            FieldType = "TEXT"
        Case 201
            FieldType = "TIMESTAMP"
        Case 128
            FieldType = "TINYINT"
        Case 17
            FieldType = "UNIQUEIDENTIFIER"
        Case 72
            FieldType = "VARBINARY"
        Case 204
            FieldType = "VARCHAR"
        Case 200
            FieldType = ""
    End Select
End Function

http://www.csdn.net/develop/Read_Article.asp?Id=15422

调试易

如何通过ADO获得数据库的字段名，字段类型？在线等。。。

解决方案 »