访问VBA参数传递查询到SQL Server

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语法，而不是Access VBA语法。试试看。

此外，这同样适用于存储过程。使用您通过SSMS执行的语法。

“EXEC sp_mysp VAR1 VAR2” 等。

Answer 2

考虑建立驻留在SQL Server中的命名存储过程，并有MS访问调用它通过使用ADO，而不是当前的DAO方法，因为你需要的参数的参数。然后结果绑定到记录：

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

Answer 3

只需创建一个通虽然在Access查询和保存。

确保PT查询工作。它可能会是这样的：

Exec的MySpName“2017年1月1日”，“2017年5月31日”

再次：100％确保在Access点击它的查询工作。此时您不写任何VBA代码。

一旦你有以上通过查询工作，然后在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 

Answer 4

艾伯特Kallal得到的答复是点上。谢谢Albert。我尝试在注册后发表评论，但...没有足够的评论，所以...希望这经过....

我唯一改变的是....

我更换了Set rst = .OpenRecordset 与...再次CurrentDb.QueryDefs("q_PTO_SubmitNewRequest").Execute

感谢张贴此。这确实是一个巨大的帮助。多年前，我有许多复杂的.adp项目，并且正在与需要类似功能的客户端合作。它看起来像我可以使用传递查询来镜像.adp功能。非常酷的:)

随着CurrentDb.QueryDefs("q_PTO_SubmitNewRequest")

.SQL = strSQL 

末随着

CurrentDb.QueryDefs("q_PTO_SubmitNewRequest").Execute