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) AS BEGIN Select * FROM qryGreenSheet WHERE OrderDate between @startdate and @enddate END
Any help is greatly appreciated.
- Moved by Mark Liu-lxfModerator Friday, June 22, 2012 5:22 AM (From:Visual Basic General)
Welcome to the MSDN forum!
Please refer to the following code:
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 Else MsgBox "Please make sure the paramters are entered!" End If End Sub 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) rs.MoveNext Loop rs.Close ' Cleanup ' Set rs = Nothing Set qf = Nothing Set db = Nothing End Sub
MSDN Community Support | Feedback to us