http://blog.csdn.net/online/archive/2004/09/08/98744.aspx 测试环境:WINXP+VB6添加2个列表框,1个按钮 '引用微软 ADO Ext.2.X for dll and SecurityDim cat As ADOX.CatalogDim cnn As ADODB.ConnectionDim tbl As ADOX.Table Private Sub Command1_Click()On Error Resume NextFor Each tbl In cat.Tables'如果是sqlserver数据库,则变成If Left(tbl.Name, 3) <> "sys"If Left(tbl.Name, 4) <> "MSys" ThenList1.AddItem tbl.NameEnd IfNextEnd Sub Private Sub Form_Load()Set cnn = New ADODB.ConnectionSet cat = New ADOX.Catalogcnn.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 = cnnEnd Sub Private Sub Form_Unload(Cancel As Integer)Set cat = NothingSet con = NothingEnd Sub
Option ExplicitPrivate 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 SubPrivate 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 SubPublic 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
以上要引用ADO对象:工程->引用Microsoft ActiveX Data Objects 2.x Library
测试环境:WINXP+VB6添加2个列表框,1个按钮 '引用微软 ADO Ext.2.X for dll and SecurityDim cat As ADOX.CatalogDim cnn As ADODB.ConnectionDim tbl As ADOX.Table Private Sub Command1_Click()On Error Resume NextFor Each tbl In cat.Tables'如果是sqlserver数据库,则变成If Left(tbl.Name, 3) <> "sys"If Left(tbl.Name, 4) <> "MSys" ThenList1.AddItem tbl.NameEnd IfNextEnd Sub Private Sub Form_Load()Set cnn = New ADODB.ConnectionSet cat = New ADOX.Catalogcnn.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 = cnnEnd Sub Private Sub Form_Unload(Cancel As Integer)Set cat = NothingSet con = NothingEnd Sub
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 SubPrivate 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 SubPublic 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