none
Using Access Form Date Input as parameters for stored procedure

    Question

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


    Jeff E

    Thursday, June 21, 2012 1:43 PM

Answers

  • Hi Jeff,

    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

    Reference from:

    http://stackoverflow.com/questions/9868362/how-to-execute-sql-server-stored-procedure-using-ms-access-form-by-passing-param

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Monday, June 25, 2012 8:08 AM
    Moderator