从 Excel 中使用 VBA 变量函数调用 SQL 查询

Calling SQL Query with VBA Variable Function from Excel

提问by Orphid

I'm a bit of newbie when it comes to these things, so apologies if this is a stupid question.

当涉及到这些事情时，我有点新手，所以如果这是一个愚蠢的问题，我深表歉意。

I need to run an SQL query from a piece of VBA. The query is a little odd, because it contains a VBA variable in its function. Otherwise everything is pretty straight forward. The VBA should call the query and then insert it into a client excel document.

我需要从一段 VBA 运行 SQL 查询。该查询有点奇怪，因为它的函数中包含一个 VBA 变量。否则一切都很简单。VBA 应调用查询，然后将其插入到客户端 excel 文档中。

Every time I run the query within Access everything is fine, the function returns the correct value and filters down the columns. Every time I run it from VBA in Excel it says "Run-Time Error "3085": Undefined Function 'CutOff' in expression.

每次我在 Access 中运行查询时，一切都很好，该函数会返回正确的值并过滤列。每次我从 Excel 中的 VBA 运行它时，它都会说"Run-Time Error "3085"：表达式中的未定义函数 'CutOff'。

I've look for info and have found old sites saying that Access 2003 sometimes has an issue doing this sort of thing, but I'm running 2010 (I think). Just hoping the problem is solve-able and greatly appreciate any advice.

我一直在寻找信息并发现旧网站说 Access 2003 有时在做这类事情时会出现问题，但我正在运行 2010（我认为）。只是希望问题是可以解决的，并且非常感谢任何建议。

The query is as follows :

查询如下：

 SELECT [<TableName>].ID...*
    FROM [<TableName>]
    WHERE ((([<TableName>].ID)>CutOff()))
    ORDER BY [<TableName>]].ID;

Public Function Cutoff()
    Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet
    Dim y As Long
    Set WB1 = Workbooks.Open("C:\filepath.z.xlsm")
    Set WS1 = WB1.Sheets("Sheet2")
    y = WS1.Range("A1").End(xlDown).Offset(0, 0).Value
    'Debug.Print y
    Cutoff = y
    'Debug.Print Cutoff
End Function 

The VBA that runs it is operated from Excel. I have tried the following:

运行它的 VBA 是从 Excel 操作的。我尝试了以下方法：

Sub Export2()

    Dim db2 As Database
    Dim rs2 As DAO.Recordset, i As Long, sFormat As String
    Dim WB2 As Excel.Workbook, WS2 As Excel.Worksheet
    Set WB2 = Workbooks.Open("C:\FilePath.z.xlsm")
    Set WS2 = WB.Sheets("Sheet2")
    Set db2 = OpenDatabase("C:\FilePath.x.mdb")
    Set qd2 = db2.QueryDefs("ExportCount")
    Set rs2 = qd2.OpenRecordset()

        If rs2.EOF Then
            GoTo EndLoop
        End If

    WS2.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs2
    WS2.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit
EndLoop:


    Set WB = Nothing
    Set WS2 = Nothing
    Set db2 = Nothing
    Set qd2 = Nothing
    Set rs2 = Nothing

End Sub

EDIT:

编辑：

Have also tried:

也试过：

Sub SQLquery1()

    Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet
    Dim wt As DAO.Database
    Dim we As DAO.Recordset
    Dim wd As DAO.QueryDef

    Set WB1 = Workbooks.Open("C:\x.xlsm")
    Set WS1 = WB1.Sheets("Sheet2")
    mySQLVariable = WS1.Range("A1").End(xlDown).Offset(0, 0).Value
    'Debug.Print mySQLVariable
    Set wt = OpenDatabase("C:\z.mdb")
    Set wd = wt.QueryDefs("ExportCount")
    Set we = wd.OpenRecordset("h")

    WS2.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset wd
    WS2.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit

    Set WB1 = Nothing
    Set WS1 = Nothing
    Set wt = Nothing
    Set we = Nothing
    Set wd = Nothing

End Sub

EDIT2

编辑2

Sub CreateQueryDef()
    Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet
    Dim dbPP As Database
    Dim qdfTemp As QueryDef
    Dim Counter As DAO.Recordset
    Dim mySQLVariable As String
    Dim rs5 As DAO.Recordset


    Set dbPP = OpenDatabase("C:\filepath\z.mdb")
    Set Counter = dbPP.OpenRecordset("j")
    Set WB1 = Workbooks.Open("C:\filepath\x.xlsm")
    Set WS1 = WB1.Sheets("Sheet2")
    mySQLVariable = WS1.Range("A1").End(xlDown).Offset(0, 0).Value
    'Debug.Print mySQLVariable

    With dbPP
        Set qdfTemp = dbPP.CreateQueryDef("NewQueryDef", "SELECT * FROM [j]")
        'WHERE ((j.[ID])=>(mySQLVariable)))") I can't get the syntax of these lines right - they are supposed to all be on the same line
        Set rs5 = qdfTemp.OpenRecordset() ' maybe Set rs5 = qdfTemp.OpenRecordset("NewQueryDef")?
    End With

        WS1.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs5
        WS1.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit
        dbPP.QueryDefs.Delete "NewQueryDef"

End Sub

Or

或者

    Sub CreateQueryDef()
            Dim dbPP As Database
            Dim qdfTemp As QueryDef
            Dim Counter As DAO.Recordset
            Dim mySQLVariable As String
            Dim rs5 As DAO.Recordset


            Set dbPP = OpenDatabase("C:\filepath\z.mdb")
            Set Counter = dbPP.OpenRecordset("j")
            mySQLVariable = CutOff
            'Debug.Print mySQLVariable

            With dbPP
                Set qdfTemp = dbPP.CreateQueryDef("NewQueryDef", "SELECT * FROM [j] WHERE ((j.[ID])=>(mySQLVariable)))")
                Set rs5 = qdfTemp.OpenRecordset("NewQueryDef")
            End With

            WS1.Range("A1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs5
            WS1.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit
            dbPP.QueryDefs.Delete "NewQueryDef"

            dbPP.Close

            Set dbPP = Nothing
            Set qdfTemp = Nothing
            Set Counter = Nothing
            Set mySQLVariable = Nothing
            Set rs5 = Nothing
End Sub

Public Function Cutoff()
        Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet
        Dim y As Long
        Set WB1 = Workbooks.Open("C:\filepath.z.xlsm")
        Set WS1 = WB1.Sheets("Sheet2")
        y = WS1.Range("A1").End(xlDown).Offset(0, 0).Value
        'Debug.Print y
        Cutoff = y
        'Debug.Print Cutoff
End Function 

采纳答案by Orphid

Worked out what I was doing wrong.

弄清楚我做错了什么。

The current value of the variable needs to be inserted into an SQL string written in VBA and passed to Access as a temporary query. The value of the variable is fixed by the time it is handed to Access, so Access doesn't need to run a macro to retrieve it, which would require the database to be open with macros enabled e.g.:

变量的当前值需要插入到用 VBA 编写的 SQL 字符串中，并作为临时查询传递给 Access。变量的值在传递给 Access 时是固定的，因此 Access 不需要运行宏来检索它，这将需要在启用宏的情况下打开数据库，例如：

    Public y As String

    Sub definey()
      y = (VariableInput)
    Call Query
    End Sub

    Sub Query
    Dim q As DAO.Database
    Dim s As DAO.Recordset
    Dim mySQLVariable As String
    Dim strSQL As String

    mySQLVariable = y

        strSQL = "SELECT * FROM [Table1] WHERE (((Table1.ID)>" & "Chr MySQLVariable Chr")) 
    'I'm free writing, not copying from code, so apologies if this isn't quite right

        Set q = OpenDatabase("Filepath\h.mdb")
        Set s = q.OpenRecordset(strSQL)

    '... then copy to workbook.
End Sub