使用ADOX方便的查询表和字段

测试环境:WINXP+VB6

添加2个列表框，1个按钮

 

'引用微软 ADO Ext.2.X for dll and Security

Dim cat As ADOX.Catalog

Dim cnn As ADODB.Connection

Dim tbl As ADOX.Table

 

Private Sub Command1_Click()

On Error Resume Next

For Each tbl In cat.Tables

'如果是sqlserver数据库，则变成If Left(tbl.Name, 3) <> "sys"

If Left(tbl.Name, 4) <> "MSys" Then

List1.AddItem tbl.Name

End If

Next

End Sub

 

Private Sub Form_Load()

Set cnn = New ADODB.Connection

Set cat = New ADOX.Catalog

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:/csdn_vb/database/article.mdb"

'cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=northwind;Data Source=yang"

Set cat.ActiveConnection = cnn

End Sub

 

Private Sub Form_Unload(Cancel As Integer)

Set cat = Nothing

Set con = Nothing

End Sub

 

Private Sub List1_Click()

Dim fld

Dim intfield As Integer

List2.Clear

intfield = cat.Tables(List1.List(List1.ListIndex)).Columns.Count

For i = 0 To intfield - 1

    Set fld = cat.Tables(List1.List(List1.ListIndex)).Columns(i)

    List2.AddItem fld.Name & " " & fld.Type & " " & fld.DefinedSize

Next

End Sub