使用下面的代码,我们可以从访问文件中提取表数据。
在此基础上,是否可以遍历 MDB 目录并提取此信息 1) 不打开每个 MDB,或 2) 打开每个 MDB 但不运行自动执行宏。
Function dmwListAllTables() As String
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim tbl As AccessObject, db As Object
Dim strMsg As String
On Error GoTo Error_Handler
Set dB = Application.CurrentData
For Each tbl In db.AllTables
Debug.Print tbl.Name
Next tbl
strMsg = " -- Tables listing complete -- "
Procedure_Done:
dmwListAllTables = strMsg
Exit Function
Error_Handler:
strMsg = Err.Number & " " & Err.Description
Resume Procedure_Done
End Function
souce: http://www.consultdmw.com/access-VBA-list-objects.htm
顺便说一句。"环通"部分不是问题:
Sub REadThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("c:myfolder")
While (file <> "")
If InStr(file, "myprefix") > 0 Then
// do something here...
End If
file = Dir
Wend
End Sub
ADO OpenSchema
方法列出 Access 数据库中的表。
该方法需要对目标数据库进行 ADO Connection
,这意味着将创建/更新锁定文件(.mdb文件的 .ldb)。 但是,如果该数据库包含 AutoExec 宏,则该宏将不会运行。
Public Sub ListTables(ByVal pFullPath As String)
Const adSchemaTables As Long = 20
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim strConnect As String
Dim strProvider As String
strProvider = CurrentProject.Connection.Provider
strConnect = "Provider=" & strProvider & ";" & _
"Data Source=" & pFullPath & ";"
Set cn = CreateObject("ADODB.Connection")
cn.Open strConnect
Set rs = cn.OpenSchema(adSchemaTables)
With rs
Do While Not .EOF
If !TABLE_TYPE <> "VIEW" Then
Debug.Print !TABLE_NAME
End If
.MoveNext
Loop
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
如果您还想查看这些项目中的每一个是链接表还是本地表,请将Debug.Print
行更改为此...
Debug.Print !TABLE_NAME, !TABLE_TYPE
有关详细信息,请参阅如何在 Visual Basic 中使用 ADO OpenSchema 方法。