如何在Access VBA中获取查询的源表列表 [英] How to get list of source tables of a query in Access VBA

问题描述

可以从记录集或查询定义中轻松检索字段列表，这样更快：

The list of fields can be retrieved easily from the recordset or from query definion which is faster:

    Private Sub SourceTablesFieldsList()
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim i As Long
    Dim str As String
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QueryName")
    
    With qdf
    For i = 0 To .Fields.count - 1
        str = "[" & .Fields(i).Name & "]"
        Debug.Print str & Space(30 - Len(str)) & _
                    "[" & .Fields(i).SourceTable & "].[" & .Fields(i).SourceField & "]"
    Next
    End With
    
    qdf.Close
    Set qdf = Nothing
    
    'from the recordset - slower
    Set rs = db.OpenRecordset("QueryName", dbOpenSnapshot)
    
    With rs 'Fields(0).Properties
    For i = 0 To .Fields.count - 1
        str = "[" & .Fields(i).Name & "]"
        Debug.Print str & Space(30 - Len(str)) & _
                    "[" & .Fields(i).SourceTable & "].[" & .Fields(i).SourceField & "]"
    Next
    End With
    
    rs.Close
    Set rs = Nothing
    
    db.Close
    Set db = Nothing
    
    End Sub

它返回类似的内容：

    [Field1Name]                  [].[]
    [Field2Name]                  [SourceTable2Name].[SourceField2Name]
    [Field3Name]                  [SourceTable3Name].[SourceField3Name]
    [Field4Name]                  [].[]
    [Field5Name]                  [].[]

其中[]。[]是

字段First（SourceTable4Name.SourceField4Name）AS Field4Name

First(SourceTable4Name.SourceField4Name) AS Field4Name

因此，当某些字段不直接从表中获取但在查询中创建时，可以省略某些表。这只是字段中使用的表的列表，但查询可以在FROM语句中包含更多表。

So some tables can be omitted when some of fields are not taken directly from tables, but created in the query. And this is only list of tables used in fields, but query can contain more tables in FROM statement.

推荐答案

您可以检索sql来自querydef的字符串如下：

You can retrieve the sql string from the querydef like this:

Sub QDFstuff（）

   Dim qd As QueryDef

  

  设置qd = CurrentDb.QueryDefs（" yourQry"）

   Debug.Print qd.SQL

Sub QDFstuff()
   Dim qd As QueryDef
  
   Set qd = CurrentDb.QueryDefs("yourQry")
   Debug.Print qd.SQL

End Sub

然后使用Instr函数解析字符串并搜索 - from - key单词和后面的单词 - from - 是源表的表名。

and then parse out the string with the Instr function and search for the -- from -- key word and the word(s) that follow -- from -- is/are the table names of the source tables.

这篇关于如何在Access VBA中获取查询的源表列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！