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

Question

使用默认参数时，Access 传递查询有效。 在 Access 报告中使用时，使用的提示根据 ptq 中的默认参数而不是应答的提示返回记录。 正在返回默认数据。

我有一个基于 SQL 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

我根据 Albert D. Kallal 的 SO 回复为 ptq 提供默认参数。

我使用 Access 选择查询qry_ptq_uspWorkCentreReport接收 [JOB NUMBER]、[REL #]、[JOB NAME]、QTY 并传递参数 TheDate(设置为"日期随时间变化")、WC(设置为"短文本")和"Shift"(设置为"整数"。

qry_ptq_uspWorkCentreReport使用传递查询。 参数是使用 Access 的"参数"小程序设置的，而不是在查询字段中设置的。 运行此选择查询会提示输入 3 个参数，但仅根据 ptq 的一行中设置的默认参数返回数据。 我没想过要看Access SQL语句，但明天早上上班时会这样做。 编辑 - 这是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=OURSNTSQL;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 代码：

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

访问查询具有参数：

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

由于它们是在查询定义中定义的，因此 Access 在打开/运行查询时会询问它们。

但是这些参数永远不会被使用！

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

因此，当您运行 Access 查询时，它将始终运行 PT 查询的保存内容，即
exec uspWorkCentreReport '2019-05-30','PCOT',1
您输入的参数将被忽略。

您需要做什么(如您所指的答案中所述)：

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

从 Access 查询中删除参数，它们对您的情况没有用。或者完全删除查询，除非您需要它与其他内容联接 PT 查询。

编辑

---

上面的编辑：

如果收到运行时错误，则.Sql中可能存在语法错误。在变量中生成 SQL 字符串，执行Debug.Print strSql，然后在 SSMS 中运行该字符串。您可能需要更改日期格式(具体取决于您的区域设置)。

另：请参阅我的第 3 个项目符号。定义临时查询定义并打开记录集不适用于报表。必须分配作为报表记录源的现有查询的.Sql。

附录：如果需要创建新查询，请先设置.Connect，然后.Sql，以便 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

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