如何在Excel中用VBA获取 Access 2007的表名

凡卓　发表于：2013-03-19 14:08:31 　

下面代码是我用Excel VBA获取Access 2003数据库中所有表名, 并将其赋值给窗体中的一个列表框控件.

Sub GetAccTableList()
Dim Cat As Object, Cnn As Object, Tbl As Object
Dim Ctr As Integer

If dbPath <> "" Then
Set Cnn = CreateObject("adodb.connection")
Set Cat = CreateObject("adox.catalog")
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";User Id=admin;Password="

Set Cat.ActiveConnection = Cnn
For Each Tbl In Cat.Tables
If Left(Tbl.Name, 4) <> "MSys" Then
UserForm1.Cmb_TbName.AddItem Tbl.Name
End If
Next Tbl
Set Cat = Nothing
Set Cnn = Nothing
End If
End Sub

现在问题是, 如果我选择的Access 2007版本的数据库文件时, 上面填充**的语句出错. 请老师们指点.

错误提示如下:

Top

煮江品茶发表于：2013-03-19 15:53:19

Sub GetAccTableList(byval dbpath as string)
    Dim rs As New ADODB.Recordset
    Dim ssql as string
    Dim i as long

if dbpath="" then exit sub

    ssql="select * from MSysObjects in '" & dbPath & "' where (Type=1 or Type=1) and ((name like 'MSys*')=false)"
    rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    for i=1 to rs.recordcount
        UserForm1.Cmb_TbName.AddItem rs!Name.value
        rs.movenext
    next

rs.close:set rs=nothing
end sub

Top

凡卓发表于：2013-03-19 21:48:25

谢谢你的指导.

请问这面这句如何理解?

"(Type=1 or Type=1)"

Top

煮江品茶发表于：2013-05-14 10:47:26

Type=1 or Type=6 '本地表或链接表

Top

总记录：3篇页次：1/1 9 1 :

Access交流中心

如何在Excel中用VBA获取 Access 2007的表名