在对 SQL Server 的直通查询中访问 VBA 参数

Access VBA Parameter in passthrough query to SQL Server

提问by TheNiers

I have several queries in an MS Access database. Some of these use parameters. I use the following code in VBA to provide the query with these parameters:

我在 MS Access 数据库中有几个查询。其中一些使用参数。我在 VBA 中使用以下代码为查询提供这些参数：

VBA

VBA

Dim startDate As Date
Dim endDate As Date

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

If IsNull(Me.dpFrom) Or IsNull(Me.dpTo) Then
    MsgBox "Please select a date!"
ElseIf (Me.dpFrom.Value > Me.dpTo.Value) Then
    MsgBox "Start date is bigger than the end date!"
Else
    startDate = Me.dpFrom.Value
    endDate = Me.dpTo.Value

    Set dbs = CurrentDb

    'Get the parameter query
        Set qdf = dbs.QueryDefs("60 Dec")

        'Supply the parameter value
        qdf.Parameters("startDate") = startDate
        qdf.Parameters("endDate") = endDate

        'Open a Recordset based on the parameter query
        Set rst = qdf.OpenRecordset()

            'Check to see if the recordset actually contains rows
        If Not (rst.EOF And rst.BOF) Then
            rst.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rst.EOF = True
                'Save contact name into a variable
                Me.tbBUDdec.Value = rst!Som
                rst.MoveNext
                Me.tbLEYdec.Value = rst!Som
                rst.MoveNext
                Me.tbMDRdec.Value = rst!Som
                rst.MoveNext
                Me.tbODCdec.Value = rst!Som
                rst.MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If
        rst.Close 'Close the recordset
        Set rst = Nothing 'Clean up

Access Query

访问查询

PARAMETERS startDate DateTime, endDate DateTime;
SELECT WarehouseCode, COUNT(DeliveryPoint) AS Som
FROM [50 resultaat]
WHERE EntryDate between [startDate] and [endDate]
GROUP BY WarehouseCode;

This is working fine. However, I am now trying to use the same code to call a passthrough query to a SQL server. This query uses a different syntax to declare and set the parameters:

这工作正常。但是，我现在尝试使用相同的代码来调用对 SQL 服务器的直通查询。此查询使用不同的语法来声明和设置参数：

SQL Server query

SQL Server 查询

DECLARE @InvLineEntryDateBegin AS date
DECLARE @InvLineEntryDateEnd AS date
SET @InvLineEntryDateBegin = '2017-01-01'
SET @InvLineEntryDateEnd = '2017-05-31'

Select WarehouseCode, Count(PickOrderNr) as Som
FROM ( bla bla bla ...

I can't get my VBA code to work with the different SQL syntax. I've read several options but couldn't find anything concrete. Does anyone have experience with this query structure?

我无法让我的 VBA 代码使用不同的 SQL 语法。我已经阅读了几个选项，但找不到任何具体的内容。有没有人有这种查询结构的经验？

In other words: How can I, in VBA, insert parameters in a stored procedure that queries on a SQL server?

换句话说：在 VBA 中，如何在查询 SQL 服务器的存储过程中插入参数？

回答by Parfait

Consider building a named stored procedure that resides in SQL Server and have MS Access call it passing parameters using ADO as opposed to your current DAO method since you require parameterization. Then bind results to a recordset:

考虑构建一个驻留在 SQL Server 中的命名存储过程，并让 MS Access 调用它使用 ADO 传递参数，而不是您当前的 DAO 方法，因为您需要参数化。然后将结果绑定到记录集：

SQL Server Stored Proc

SQL Server 存储过程

CREATE PROCEDURE myStoredProc 
   @InvLineEntryDateBegin DATE = '2017-01-01',
   @InvLineEntryDateEnd DATE = '2017-05-31'
AS

BEGIN
   SET NOCOUNT ON;    

   SELECT WarehouseCode, Count(PickOrderNr) as Som
   FROM ( bla bla bla ... ;

END

VBA

VBA

' SET REFERENCE TO Microsoft ActiveX Data Object #.# Library
Dim conn As ADODB.Connection, cmd As ADODB.Command, rst As ADODB.Recordset
Dim startDate As Date, endDate As Date   

If IsNull(Me.dpFrom) Or IsNull(Me.dpTo) Then
    MsgBox "Please select a date!", vbCritical, "MISSING DATE"
    Exit Sub
End if    
If (Me.dpFrom.Value > Me.dpTo.Value) Then
    MsgBox "Start date is bigger than the end date!", vbCritical, "INCORRECT RANGE"
    Exit Sub
End if

startDate = Me.dpFrom.Value: endDate = Me.dpTo.Value

' OPEN CONNECTION
Set conn = New ADODB.Connection         
conn.Open "DRIVER={SQL Server};server=servername;database=databasename;UID=username;PWD=password;"

' OPEN/DEFINE COMMAND OBJECT
Set cmd = New ADODB.Command     
With cmd
    .ActiveConnection = conn
    .CommandText = "myStoredProc"
    .CommandType = adCmdStoredProc

    ' BIND PARAMETERS
    .Parameters.Append .CreateParameter("@InvLineEntryDateBegin", adDate, adParamInput, 0, startDate)
    .Parameters.Append .CreateParameter("@InvLineEntryDateEnd", adDate, adParamInput, 0, endDate)
En With

' BIND RESULTS TO RECORDSET
Set rst = cmd.Execute
...

回答by Albert D. Kallal

Simply create a pass-though query in Access and save it.

只需在 Access 中创建传递查询并保存即可。

Ensure that the PT query works. It will likely look like:

确保 PT 查询有效。它可能看起来像：

Exec MySpName '2017-01-01', '2017-05-31'

Exec MySpName '2017-01-01', '2017-05-31'

Again: 100% Make sure the query works when you click on it in Access. At this point you not written any VBA code.

再次：100% 确保在 Access 中单击查询时查询有效。此时您还没有编写任何 VBA 代码。

Once you have above pass through query working, then in VBA you can do this:

一旦你通过上面的查询工作，那么在 VBA 中你可以这样做：

Dim strStartDate    As String
Dim strEndDate      As String
Dim strSQL          As String

strStartDate = "'" & Format(Me.dpFrom, "yyyy-mm-dd") & "'"
strEndDate = "'" & Format(Me.dpTo, "yyyy-mm-dd") & "'"


strSQL = "exec MyStoreProc " & strStartDate & "," & strEndDate

With CurrentDb.QueryDefs("QryMyPass")

  .SQL = strSQL
  Set rst = .OpenRecordset

End With

回答by RICHARD Pietrzak

If I remember right, in a pass-through query, you are passing the query definition directly to the engine in which it is going to run. So, you will have to use the SQL Server syntax for your query instead of the Access VBA syntax. Give that a try.

如果我没记错的话，在传递查询中，您将查询定义直接传递给它将运行的引擎。因此，您必须对查询使用 SQL Server 语法，而不是 Access VBA 语法。试一试吧。

Also, the same goes for a Stored procedure. Use the syntax like you were to execute through SSMS.

同样，存储过程也是如此。使用您通过 SSMS 执行的语法。

"exec sp_mysp var1 var2" and so on.

“exec sp_mysp var1 var2”等等。

回答by Bob

The reply from Albert Kallal was spot on. Thanks Albert. I tried to comment after signing up but... did not have enough points to comment so... Hoping this goes through....

Albert Kallal 的答复是正确的。谢谢阿尔伯特。注册后我尝试发表评论，但...没有足够的积分来发表评论所以...希望这能通过....

The only thing I changed was....

我唯一改变的是......

I replaced the Set rst = .OpenRecordsetwith...CurrentDb.QueryDefs("q_PTO_SubmitNewRequest").Execute

我Set rst = .OpenRecordset用...代替CurrentDb.QueryDefs("q_PTO_SubmitNewRequest").Execute

Thanks again for posting this. It really was a HUGE help. I had many complex .adp projects years ago and am working with a client needing similar functionality. It looks like I can mirror the .adp functionality using the pass through queries. Very Cool :)

再次感谢您发布此信息。这真的是一个巨大的帮助。几年前我有许多复杂的 .adp 项目，并且正在与需要类似功能的客户合作。看起来我可以使用传递查询来镜像 .adp 功能。很酷 ：）

With CurrentDb.QueryDefs("q_PTO_SubmitNewRequest")

和 CurrentDb.QueryDefs("q_PTO_SubmitNewRequest")

  .SQL = strSQL

End With

结束于

CurrentDb.QueryDefs("q_PTO_SubmitNewRequest").Execute