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

flyland 2004-09-13 03:25:23
如何取得mdb、xls等数据库里面所有表的名称?
...全文
84 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
RUKYO 2004-09-13
以上要引用ADO对象:

工程->引用Microsoft ActiveX Data Objects 2.x Library
回复
RUKYO 2004-09-13
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
回复
online 2004-09-13
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

回复
lyc0553 2004-09-13
sp_tables
回复
RUKYO 2004-09-13
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=8159
回复
相关推荐
发帖

1187

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2004-09-13 03:25
社区公告
暂无公告