用ADO连接数据源后,如何返回指定表的字段和字段属性?请各位大侠多多指点!

[leen_yang]

用ADO连接数据源后,如何返回指定表的字段和字段属性到ListBox中?
如:表ABCD,有字段A,B,C,D,E... 字段A属性为:INTGER,长度为8...
我是这样连接并返回表名的:

Private Sub Form_Load()
ListTables
End Sub


Private Sub ListTables()
Dim rstSchema
Dim cnn1, cnn1str

List1.Clear
Set cnn1 = CreateObject("ADODB.Connection")
cnn1str = "User ID=abcd;Password=abcd;Data Source=abcd"
cnn1.Open cnn1str
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
List1.AddItem rstSchema!TABLE_NAME
rstSchema.MoveNext

Loop
rstSchema.Close
cnn1.Close

End Sub

[playyuer]

'这是我以前编的根据 Access 表结构 生成 SQL Server 的 SQL DDL 语句的代码:
'引用 Microsoft ADO Ext. 2.X for DDL and Security
Private Sub Command1_Click()
Dim adoConnection As New ADODB.Connection
Dim CatalogX As New ADOX.Catalog
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\NWind97.mdb;Persist Security Info=False"
Set CatalogX.ActiveConnection = adoConnection
Dim sSQL As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim sColumnsList As String
For i = 0 To CatalogX.Tables.Count - 1
If CatalogX.Tables.Item(i).Type = "TABLE" Then
sSQL = "CREATE TABLE [" & CatalogX.Tables.Item(i).Name & "] ("
For j = 0 To CatalogX.Tables.Item(i).Columns.Count - 1
sSQL = sSQL & "[" & CatalogX.Tables.Item(i).Columns.Item(j).Name & "] " & sGetSQLDataType(CatalogX.Tables.Item(i).Columns.Item(j).Type) & IIf(CatalogX.Tables.Item(i).Columns.Item(j).Properties.Item("Autoincrement").Value, " IDENTITY (1, 1) ", "") & IIf(CatalogX.Tables.Item(i).Columns.Item(j).Attributes = adColFixed, " NOT Null", "") & IIf(j < CatalogX.Tables.Item(i).Columns.Count - 1, ",", ")")
Next j
If CatalogX.Tables.Item(i).Keys.Count > 0 Then
For j = 0 To CatalogX.Tables.Item(i).Keys.Count - 1
For k = 0 To CatalogX.Tables.Item(i).Keys.Item(j).Columns.Count - 1
If CatalogX.Tables.Item(i).Keys.Item(j).Type = adKeyPrimary Then
If CatalogX.Tables.Item(i).Columns.Item(j).Attributes <> adColFixed Then
sSQL = sSQL & vbCrLf & "GO" & vbCrLf & "ALTER TABLE [" & CatalogX.Tables.Item(i).Name & "] ALTER COlUMN [" & CatalogX.Tables.Item(i).Keys.Item(j).Columns.Item(k).Name & "] " & sGetSQLDataType(CatalogX.Tables.Item(i).Columns(CatalogX.Tables.Item(i).Keys.Item(j).Columns.Item(k).Name).Type) & " NOT NULL"
End If
sColumnsList = sColumnsList & CatalogX.Tables.Item(i).Keys.Item(j).Columns.Item(k).Name & IIf(k < CatalogX.Tables.Item(i).Keys.Item(j).Columns.Count - 1, ",", "")
End If
Next k
Next j
sSQL = sSQL & vbCrLf & "GO" & vbCrLf & "ALTER TABLE [" & CatalogX.Tables.Item(i).Name & "] ADD CONSTRAINT " & CatalogX.Tables.Item(i).Name & "_PK PRIMARY KEY (" & sColumnsList & ")"
End If
Debug.Print sSQL
MsgBox sSQL
End If
Next i
MsgBox "OK!"
End Sub

Public Function sGetSQLDataType(ADODBDataType As ADODB.DataTypeEnum) As String
Select Case ADODBDataType
Case adBigInt, adInteger, adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
sGetSQLDataType = "INT"
Case adVarChar, adVarWChar
sGetSQLDataType = "VARCHAR(50)"
Case adLongVarWChar
sGetSQLDataType = "TEXT"
Case adCurrency
sGetSQLDataType = "MONEY"
Case adBoolean
sGetSQLDataType = "BIT"
Case Else
sGetSQLDataType = "UnKnownDataType"
Debug.Print ADODBDataType
End Select
End Function

[liuchunpeng]

建议使用Microsoft DataGrid Control，
在控件里。

[mazhayang]

同意playyuer(女㊣爱)　的方法
Dao的关于库结构和权限设定大部分语法，，用ADOX可以平稳的转化过来