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

问题描述

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

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:

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

访问查询

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

这很好.但是，我现在正尝试使用相同的代码来调用SQL Server的传递查询.该查询使用不同的语法来声明和设置参数:

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 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语法一起使用.我读过几个选项，但找不到任何具体的东西.有人对这种查询结构有经验吗?

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 Server上查询的存储过程中?

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

推荐答案

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

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存储过程

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

这篇关于在直通查询中访问VBA参数到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！