sql-server - 在对 SQL Server 的直通查询中 Access VBA 参数

Question

我在 MS Access 数据库中有几个查询。其中一些使用参数。我在 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 查询

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

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

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 ...

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

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

Answer 1

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

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

' 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
...