I saw a post from 2007 how to pass a parameter from access to sql server from pgill on the old website. I have access 2016 and was able to get the code to work with this access version, so I am sharing it.
Private Sub Command11_Click()
Dim d As Database
Dim q As QueryDef
Dim cnn As ADODB.Connection
Dim strConn As String
Set cnn = New ADODB.Connection
cnn.ConnectionString = "ODBC;DATABASE=invoice;UID=APIADMIN;PWD=xxxxx;DSN=INVOICE"
cnn.Open
' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "CONNECTED!"
Else
MsgBox "Sorry. We are not working today."
End If
' Close the connection.
cnn.Close
Set d = CurrentDb
Set q = d.QueryDefs("qry11")
q.ReturnsRecords = True
q.SQL = "Exec getorders1 " & Forms![Form11]![YEAR] & "," & Forms![Form11]![BILLAD] & ", '" & Forms![Form11]![STARTDATE] & "', '" & Forms![Form11]![ENDDATE] & "'"
'q.SQL = "Exec getorders1 " & "2017,met, '03/19/2017', '03/20/2017'"
DoCmd.OpenQuery "qry11", , acReadOnly
DoCmd.OpenQuery "qryMakeGetOrdersTable", , acReadOnly
DoCmd.Close acQuery, "qry11"
DoCmd.Close acQuery, "qryMakeGetOrdersTable"
DoCmd.Close acForm, "Form11"
End Sub