sql - 如何使用我从查询中保存到字符串而不是 VBA 中的表中的 SQL 语句创建记录集？适用于 tbl 不适用于 qry

Question

大家下午好！我在将 VBA 代码从引用表更改为引用使用 SQL 语句创建记录集的查询时遇到问题。

这是我最初编写的代码，它工作并完成了工作：

    Dim db As Database
    Set db = CurrentDb
    
    If HS = "H" Then
        startYear = DLookup("StartYearID", "dbo_H", "HID = " & ID)
        endYear = startYear + 4
        strSQL = "SELECT * FROM dbo_Costs_H WHERE (HID = " & ID & " AND YearID >= " & startYear & " AND YearID <= " & endYear & " AND ExcursionID = " & ExcID & ")"
    ElseIf HS = "S" Then
        startYear = DLookup("StartYearID", "dbo_S", "SID = " & ID)
        endYear = startYear + 4
        strSQL = "SELECT * FROM dbo_Costs_S WHERE (SID = " & ID & " AND YearID >= " & startYear & " AND YearID <= " & endYear & " AND ExcursionID = " & ExcID & ")"
    End If
    
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rs.BOF Then
        rs.MoveFirst
    End If

现在我需要将 dbo_Cost_H 和 dbo_Cost_S 更改为 qry_Cost_H 和 qry_Cost_S 才能访问我添加到这些查询中的新计算列。我编写了以下代码并且它可以编译，但它在我链接到此代码的表单上提取了不正确的数据。我认为这是因为我有Set qdf = db.QueryDefs("qry_Costs_H")行，而不是我之前从表中提取时的 SQL 语句。

这是我目前正在使用的代码：

Dim db As Database
Set db = CurrentDb

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As Recordset

If HS = "H" Then
    startYear = DLookup("StartYearID", "dbo_H", "HID = " & ID)
    endYear = startYear + 4
    strSQL = "SELECT * FROM qry_Costs_H WHERE (HID = " & ID & " AND YearID >= " & startYear & " AND YearID <= " & endYear & " AND ExcursionID = " & ExcID & ")"
    Set qdf = db.QueryDefs("qry_Costs_H")
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
    Set rs = qdf.OpenRecordset
    If Not rs.BOF Then
        rs.MoveFirst
    End If
ElseIf HS = "S" Then
    startYear = DLookup("StartYearID", "dbo_S", "SID = " & ID)
    endYear = startYear + 4
    strSQL = "SELECT * FROM qry_Costs_S WHERE (SID = " & ID & " AND YearID >= " & startYear & " AND YearID <= " & endYear & " AND ExcursionID = " & ExcID & ")"
    Set qdf = db.QueryDefs("qry_Costs_S")
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
    Set rs = qdf.OpenRecordset
    If Not rs.BOF Then
        rs.MoveFirst
    End If
End If

如果可以的话请帮忙！我需要第二段代码才能通过查询获取该 SQL 语句，就像我在第一段代码中通过表格所做的那样

标签： sqlvbams-access