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
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 Server query
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?
In other words: How can I, in VBA, insert parameters in a stored procedure that queries on a SQL server?
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 Stored Proc
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
...