I am working in an Access 2010 data project with SQL Server 2008R2. I have created a stored procedure that calls all of the data in a view within a range of dates input into the stored procedure. What I would like to do is to use a form in Access
to input the dates, and have the form pass these dates into the stored procedure parameters. I would then like to populate a report using the data returned from the stored procedure. I am new to VB and I am not sure where to start in terms of having
the dates passed into the stored procedure as well as converting them into the date format necessary for SQL. As of now when I try to use the form input to pass along the dates, the stored procedure still pops up and prompts for the start and end dates. This
is my stored procedure:
ALTER Procedure [dbo].[OrderDateRangeCommissionSheet]
(@startdate date, @enddate date)
Select * FROM qryGreenSheet
WHERE OrderDate between @startdate and @enddate
Private Sub cmdDateRange_Click()
Dim startDate As Date
Dim endDate As Date
If (Not IsNull(Me.txtStart.value)) And (Not IsNull(Me.txtEnd.value)) Then
startDate = Me.txtStart.value
endDate = Me.txtEnd.value
TestStoreProcedure startDate, endDate
MsgBox "Please make sure the paramters are entered!"
Sub TestStoreProcedure(startDate As Date, endDate As Date)
Dim db As DAO.Database
Dim qf As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set qf = db.CreateQueryDef("")
' Set to true if your storeproc returns somthing, otherwise, '
' set to False '
qf.ReturnsRecords = True
' You need to adjust this to whatever your SQL server instance name is '
' and whatever your database name is '
' This connection string will use the local machine's user credentials '
' to connect to the server, change as appropriate '
qf.Connect = "ODBC;DRIVER=SQL Server;SERVER= Your Server Name;DATABASE=Test;Trusted_Connection=Yes"
' We construct the SQL to call the procedure. Update this to suit your '
' actual proc name '
qf.SQL = "dbo.DateRangeProc '" & Format(startDate, "yyyy-mm-dd") & "'," & _
"'" & Format(endDate, "yyyy-mm-dd") & "'"
' Open the recordset to access the results '
Set rs = qf.OpenRecordset()
' Print the result to the debug console '
' Of course, you need to adapt this to your own case '
Do While Not rs.EOF
Debug.Print rs(0) & " " & rs(1) & " " & rs(2) & " " & rs(3)
' Cleanup '
Set rs = Nothing
Set qf = Nothing
Set db = Nothing