CODEWITHSUNDEEP
sql-server-2008ms-access-2007
user1292427
user1292427

Reputation: 21

how to execute Sql server stored procedure using Ms-Access form by passing parameters

i need to develop Access form to pass parameters to call Sql Server Stored procedure.and need to show output. Ex: i have two parameters " Start Date " And " End Date " from Access form i need to pass these dates and excute Sql server Stored procedure and output should display..

Please help me...Please help me step by step,I am new to This Access application

Upvotes: 2

Views: 14985

Answers (2)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

It depends on your stored procedure output type, but basically, say you want to display the result of your stored procedure myProc to the debug console, you could do something like:

Sub printMyProcResults(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 stored procedure returns something, 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=MYSERVER;Trusted_Connection=Yes;DATABASE=MYDATABASE;"

    ' We construct the SQL to call the procedure. Update this to suit your '
    ' actual proc name '
    qf.SQL = "myStoredProc '" & Format(startDate, "dd mmm yyyy") & "'," & _
                          "'" & Format(endDate, "dd mmm yyyy") & "'" 

    ' 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.MoveNext
    Loop
    rs.Close
    ' Cleanup ' 
    Set rs = Nothing
    Set qf = Nothing
    Set db = Nothing
End Sub

For the connection string, you may need to adapt it to your own settings, depending on how your SQL Server is configured: http://connectionstrings.com/sql-server-2008

Upvotes: 3

Namphibian
Namphibian

Reputation: 12221

I did something similar in access years ago. You would need to look at pass through ODBC queries. This will allow you to execute SQL stored procedure from Access.

Please see this link: http://support.microsoft.com/kb/303968

I am not sure how you will get the parameters passed through though but I am sure if you google " pass through odbc query access stored procedure parameters " you will find some hints. Sorry my memory is a bit faded about the exact details.

Upvotes: 0

Related Questions