如何取得mdb、xls等数据库里面所有表的名称？

[flyland]

如何取得mdb、xls等数据库里面所有表的名称？

[RUKYO]

http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=8159

[lyc0553]

sp_tables

[online]

http://blog.csdn.net/online/archive/2004/09/08/98744.aspx
测试环境: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

[RUKYO]

Option Explicit

Private Sub Command1_Click() '获得Access表名
Dim mCnnString As String
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False"
Call mGetTableName(mCnnString)
End Sub

Private Sub Command2_Click() '获得Excel表名
Dim mCnnString As String
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & App.Path & "\Book1.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
Call mGetTableName(mCnnString)
End Sub

Public Sub mGetTableName(ByVal mSendString As String)
Dim mCon As New ADODB.Connection
Dim mSchema As New ADODB.Recordset
mCon.Open mSendString
Set mSchema = mCon.OpenSchema(adSchemaTables)
Do Until mSchema.EOF
If mSchema!TABLE_TYPE = "TABLE" Then Debug.Print "Table name: " & mSchema!TABLE_NAME & vbCr & "Table type: " & mSchema!TABLE_TYPE & vbCr
mSchema.MoveNext
Loop
Set mSchema = Nothing
Set mCon = Nothing
End Sub

[RUKYO]

以上要引用ADO对象：

工程->引用Microsoft ActiveX Data Objects 2.x Library