How to execute stored procedure from Access?

[Mayo123]

I have created a stored procedure with parameter in SQL server 2000. How can I execute it from Access.
Thanks for your help!

 

[jsteph]

If the parameter is an 'in' only, then the quick way is to use a pass-thru query. If you are returning a value from the parameter, then you'll need to look into ADO, using the command object.
--Jim

 

[cmmrfrds]

Tell us what you want to do. Bring the results back to a recordset in vba code, bind the results to a Form, display the results in a Report???

 

[Mayo123]

Based on user's input(ssn),the related record would be shown on a form. How can I pass this ssn to my stored procedure?
Thanks for your help!

 

[RobertT687]

Not elegant, but it has the virtue of working.
Attach this code to a button on your form.

Private Sub MakePassThru()
Dim MyQuery    As QueryDef
Dim ConnectStr As String
Dim SqlCmd     As String
Dim SSNParm    As String

    On Error Resume Next
    DoCmd.DeleteObject acQuery, "YourQueryNameHere"
    SSNParm = Me!YourFormFieldWithTheSSN
'
    On Error GoTo MakePassThru_Err
'
    Set MyQuery = CurrentDb.CreateQueryDef("YourQueryNameHere")
    ConnectStr = "ODBC;DSN=YourDSN;UID=YourUserID;PWD='YourPassword';LANGUAGE=us_english;DATABASE=YourDatabase"
    SqlCmd = "EXECUTE YourProcedureName '" & SSNParm & "'"
    
    With MyQuery
        .Connect = ConnectStr
        .SQL = SqlCmd 
        .ODBCTimeout = 0 
        .ReturnsRecords = True 
    End With
    MyQuery.Close
'
MakePassThru_Exit:
    Exit Sub
MakePassThru_Err:
    MsgBox Err.Description
    Resume MakePassThru_Exit

This will create a pass-thru query with the required parameter.
You can then bind the query as the datasource for a form or report.

 

[danvlas]

Set oRst = CurrentProject.Connection.Execute("StoredProcName(" & me.ssn & ")")

Do whatever you like with the resulting recordset.

The line of code assumes the current user has privileges to execute the procedure and that ssn is a number.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html