MS Access OpenRedcordset读取错误的字符串 [英] MS Access OpenRedcordset reading wrong string

问题描述

一般说明:

1. 我的查询 2_Total 返回一个值:

运行将查询导出到excel文件的VBA函数:

Run the VBA function that exports the query to an excel file:

问题: OpenRecordset没有读取正确的查询.我的查询应导出到我的excel文件.但是我的VBA指向错误的内容.

Problem: OpenRecordset is not reading the correct query. My query should be exported to my excel file. Yet My VBA is pointing at wrong stuff.

我的猜测是qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"行没有提供足够的信息?

My guess is that the line qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]" is not providing sufficient information?

SQL

SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) 
BETWEEN [Forms]![RUN]![textBeginOrderDate] AND [Forms]![RUN]![textendorderdate]));

VBA

Option Compare Database

Option Explicit
Public Function TRANS2()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer

    Dim db As DAO.Database
    Dim qry As QueryDef
    Dim rst As Recordset
    Dim prm As DAO.Parameter
    Dim strSQL As String

    Set db = CurrentDb
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Users\J\Desktop\August 2017.xlsx")
    Set xlWS = xlWB.Worksheets("Totals")

    xlRow = (xlWS.Columns("K").End(xlDown).Row)
    Set qry = db.QueryDefs("2_Total")

    strSQL = strSQL & " [dbo_SO_SalesHistory].[InvoiceDate] Between #" _
    & [Forms]![Run]![textBeginOrderDate] & "# And #" _
    & [Forms]![Run]![textendorderdate] & "#"

    qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"

    Set rst = db.OpenRecordset("2_Total", dbOpenDynaset)

    Dim c As Integer
    c = 11   'C is the one that stores column number, in which c=1 means column A, 11 is for column K, 12 for Column L
    xlRow = xlRow + 11

     Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
        If xlRow > 25 Then GoTo rq_Exit
    Loop


rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

End Function

推荐答案

请考虑在OpenRecordset之前释放 querydef ，因为记录集调用使用了最后保存的查询实例，而不是对SQL的更改因为您从未正式保存过此文件:

Consider releasing the querydef before OpenRecordset as the recordset call uses the last saved instance of query and not the changes to the SQL since you never official save it:

qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"
Set qry = Nothing

Set rst = db.OpenRecordset("2_Total", dbOpenDynaset)

或者更好的是，使用 QueryDef直接从querydef打开记录集.OpenRecordset :

qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"

Set rst = qry.OpenRecordset(dbOpenDynaset)

---

但是在@ThomasG注释时，请仔细检查您的代码并相应地整合上述建议.该SELECT语句似乎未输出单行一列的结果集.但是，您发布的SQL确实会返回一行/一列的汇总.我怀疑您打算这样做:

---

But as @ThomasG comments, carefully check your code and integrate above suggestion accordingly. This SELECT statement does not seem to output a one-row, one-column resultset. However your posted SQL does return one row/one-column aggregate. I suspect you intended:

strSQL = "SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold" _
          & " FROM dbo_SO_SalesHistory" _
          & " WHERE [dbo_SO_SalesHistory].[InvoiceDate] BETWEEN #" _
          & [Forms]![Run]![textBeginOrderDate] & "# AND #" _
          & [Forms]![Run]![textendorderdate] & "#"

qry.SQL = strSQL

但是，我建议对更整洁，更安全，可维护的代码使用参数化.

However, I suggest using parameterization for cleaner, safer, maintainable code.

strSQL = "PARAMETERS [BeginDate] Datetime, [EndDate] Datetime;" _
          & " SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold" _
          & " FROM dbo_SO_SalesHistory" _
          & " WHERE [dbo_SO_SalesHistory].[InvoiceDate]" _
          & " BETWEEN [BeginDate] AND [EndDate];"

qry.SQL = strSQL

qry![BeginDate] = [Forms]![Run]![textBeginOrderDate]
qry![EndDate] = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

是的，上面的VBA字符串可以并且应该单独保存为Access查询对象.因此，无需每次都重新编写SQL.每次都绑定不同的动态参数！

And yes, above VBA string can and should be saved on its own as an Access query object. So no need to re-write SQL each time. Just bind different dynamic params each time!

Set qry = db.QueryDefs("2_Total")     ' ABOVE STRING SQL STATEMENT WITH PARAMETERS

qry![BeginDate] = [Forms]![Run]![textBeginOrderDate]
qry![EndDate] = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

这篇关于MS Access OpenRedcordset读取错误的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！