不使用提示参数更新参数的访问传递查询

Question

当使用默认参数时，访问传递查询可以工作。在访问报告中使用时，使用的提示将根据ptq中的默认参数而不是已回答的提示返回记录。正在返回默认数据。

我有一个基于Server的存储过程uspWorkCentreReport，它使用@TheDate DATE, @WC VARCHAR(15), @Shift INT作为参数，并通过SELECT语句返回以下列：

[JOB NUMBER], [REL #], [JOB NAME], QTY.  

下面是存储过程代码的ALTER行：

ALTER PROCEDURE [dbo].[uspWorkCentreReport]
     @TheDate DATE,
     @WC VARCHAR(15),
     @Shift INT

访问传递查询ptq_uspWorkCentreReport传递这些默认参数'2019-05-30'，'PCOT',1，并使用无DSN的ODBC连接来返回默认数据。我忘了尝试，但我认为它会返回正确的数据，无论我用什么默认参数来替换'2019-05-30'，'PCOT',1.编辑-我今天早上试过了，实际上，任何适当的替换参数都会返回适当的相关记录。这是ptq的一行：

exec uspWorkCentreReport '2019-05-30','PCOT',1

我为ptq提供了基于Albert D. Kallal's SO reply的默认参数。

我使用Access select查询qry_ptq_uspWorkCentreReport来接收作业编号、REL #、作业名称、QTY和传递参数TheDate、设置日期随时间、WC、设置为短文本和Shift，设置为Integer。

qry_ptq_uspWorkCentreReport使用传递查询.参数是使用Access的参数applet设置的，而不是在查询字段中设置的。运行此select查询将提示输入3个参数，但仅根据ptq的一行中设置的默认参数返回数据。我没有想过要查看Access语句，但是当我明天早上上班时会这样做的。编辑-以下是用于qry_ptq_uspWorkCentreReport的SQL语句：

PARAMETERS TheDate DateTime, WC Text ( 255 ), Shift Short;
SELECT ptq_uspWorkCentreReport.[JOB NUMBER], ptq_uspWorkCentreReport.[REL #], ptq_uspWorkCentreReport.[JOB NAME], ptq_uspWorkCentreReport.QTY
FROM ptq_uspWorkCentreReport;

当然，上述三个功能最终形成了一个Access报告，即rpt_qry_ptq_WorkCentreReport，以使记录具有可读性。

我在另一个报告中使用了相同的场景，将从日期和日期作为参数。当该报告运行时，提示将根据这些日期(而不是ptq中的日期)获取日期和返回记录。这是那个ptq：

exec uspMergeAandPJobs '2018-01-01','2019-01-01'

实际上，我试着用

exec uspMergeAandPJobs '',''

报告将返回0条记录！

不知道我错过了什么，希望得到任何反馈。蒂娅。

在导师的帮助下，我尝试了以下几点：

Sub Report_Load()

    Dim strFromDate     As String
    Dim strToDate       As String
    Dim strWC           As String
    Dim intShift        As Integer
    Dim strSQL          As String

    strFromDate = InputBox("From Date and Time: ")
    strToDate = InputBox("Enter To Date and Time: ")
    strWC = InputBox("Enter Work Center: ")
    intShift = InputBox("Enter Shift: ")

    Dim qdf As DAO.QueryDef, rst As DAO.Recordset
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.SQL = "exec dbo.uspWorkCentreReport " & "'" & strFromDate & "', " & "'" & strToDate & "', " & "'" & strWC & "', " & intShift & ";"

    qdf.Connect = "ODBC;DRIVER=ODBC Driver 13 for SQL Server;SERVER=OURS\NTSQL;Trusted_Connection=Yes;DATABASE=TablesCoE;ApplicationIntent=READONLY;"

    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset

    rst.Close
    Set rst = Nothing
    Set qdf = Nothing

End Sub

提示VBA后，将出现运行时错误3129 -无效的SQL语句；预期的“删除”、“插入”、“过程”、“选择”或“更新”。我们两个人都无法确定是什么导致了这一错误。在VBA中的“qdf.SQL.”线条用黄色高亮显示。

编辑-添加存储的proc的SQL代码：

ALTER PROCEDURE [dbo].[uspWorkCentreReport_TEST] @FromDate DATETIME,@ToDate DATETIME,@WC VARCHAR(15),@Shift INT

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--  Build table variable SumTable structure
DECLARE @SumTable TABLE(matl_nbr VARCHAR(60),QTY DECIMAL(4,0),matl_dsc VARCHAR(50))

--  P jobs and their summed WorkCentre traversals using crosstab - each traversal is added up
INSERT INTO @SumTable(matl_nbr,matl_dsc,QTY)
SELECT     SRC1.matl_nbr,SRC1.matl_dsc,
    SUM(CASE WHEN SRC1.locn_to = @WC THEN 1 ELSE 0 END) AS QTY
FROM 
(
SELECT matl_nbr,matl_dsc,locn_to
FROM mtrk_CompanyE.dbo.trxn_hstd th
WHERE (last_upd >= @FromDate AND last_upd <= @ToDate) AND
    locn_to = @WC
)SRC1
GROUP BY matl_nbr,matl_dsc

--  These updates take all the summed WorkCentre (locn_to) columns and turn each into "1" for later summing
UPDATE @SumTable
SET QTY = 1 
WHERE QTY >1

--  Shortening the material number from 123456_00_00_R1_00 to 1234560
UPDATE @SumTable 
SET matl_nbr = LEFT(matl_nbr,6) + right(LEFT(matl_nbr,9),1)

SELECT LEFT(A.matl_nbr,6)[JOB NUMBER],SUBSTRING(A.matl_nbr,7,1)[REL #],matl_dsc AS [JOB NAME],QTY
FROM (SELECT matl_nbr,matl_dsc,
        SUM(CASE WHEN QTY = 1 THEN 1 ELSE NULL END) AS QTY
FROM @SumTable
GROUP BY matl_nbr,matl_dsc)A
ORDER BY QTY DESC;

END

编辑-完成的子：

Private Sub Report_Open(Cancel As Integer)

    Dim strFromDate     As String
    Dim strToDate       As String
    Dim strWC           As String
    Dim intShift        As Integer
    Dim strSQL          As String

    strFromDate = InputBox("Enter From Date and Time: ")
    strToDate = InputBox("Enter To Date and Time: ")
    strWC = InputBox("Enter Work Center: ")
    intShift = InputBox("Enter Shift: ")

    strSQL = "exec dbo.uspWorkCentreReport_TEST " & "'" & strFromDate & "', " & "'" & strToDate & "', " & "'" & strWC & "', " & intShift & ";"

    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQL

    DoCmd.OpenReport "rpt_qry_ptq_uspWorkCentreReport", acViewReport

    Me.lblFromDate.Caption = strFromDate
    Me.lblToDate.Caption = strToDate
    Me.lblWC.Caption = strWC
    Me.lblShift.Caption = intShift

End Sub

Answer 1

Access查询具有以下参数：

PARAMETERS TheDate DateTime, WC Text ( 255 ), Shift Short;

由于它们是在查询定义中定义的，所以Access在打开/运行查询时会请求它们。

，但这些参数从未被使用过！

访问无法将这些参数传递到作为Access查询基础的传递查询中。同样，PT查询只不过是一个Connect字符串和一个常量SQL字符串。

因此，当您运行Access查询时，它将始终运行保存的PT查询内容，即

exec uspWorkCentreReport '2019-05-30','PCOT',1

您输入的参数将被忽略。

您需要做的事情(如您所指的answer中所概述的)：

• 创建一个窗体来收集参数值。
• 使用VBA动态创建PT查询的SQL字符串
• 将该SQL分配给PT查询： CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSql (自动保存)
• 然后您可以基于Access查询运行报表--或者更好:直接使用PT查询作为报表的记录源。

从Access查询中删除参数，它们对您的情况毫无用处。或者完全删除查询，除非您需要它以其他方式加入PT查询。

用于上述编辑的编辑：

如果您得到一个运行时错误，您的.Sql中可能会出现语法错误。在变量中构建SQL字符串，执行Debug.Print strSql，并在SSMS中运行该字符串。您可能需要更改日期格式(取决于您的区域设置)。

还有:看我的第三颗子弹。定义临时querydef并打开记录集对报表无效。必须分配作为报表记录源的.Sql现有查询。

增编:如果您需要创建一个新的查询，first set .Connect和然后 .Sql，那么Access就知道这是一个传递查询。

Access SQL不知道exec。

编辑2

您有一个现有的正在工作的PT查询ptq_uspWorkCentreReport，它返回一组参数的记录。

exec uspWorkCentreReport '2019-05-30','PCOT',1

将此查询用作报表.的记录源。

若要运行具有不同参数的报表，必须修改查询的SQL。您可以在查询设计视图中或使用VBA手动完成此操作。

我认为Report_Load()修改其记录源( PT查询)为时已晚。运行以下子程序，然后打开报表。

Sub SetUspParameters()

    Dim strFromDate     As String
    Dim strToDate       As String
    Dim strWC           As String
    Dim intShift        As Integer
    Dim strSQL          As String

    strFromDate = InputBox("From Date and Time: ")
    strToDate = InputBox("Enter To Date and Time: ")
    strWC = InputBox("Enter Work Center: ")
    intShift = InputBox("Enter Shift: ")

    strSQL = "exec dbo.uspWorkCentreReport " & "'" & strFromDate & "', " & "'" & strToDate & "', " & "'" & strWC & "', " & intShift & ";"

    Debug.Print strSQL

    ' This line is all that's needed to modify the PT query
    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQL

End Sub

实际上，您不想使用4x InputBox，而是使用一个表单。