Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Red face Unanswered: Stored Procedures in Access

    I have a Sproc on Access that runs an internal query. I don't want it to return any values. Everytime I run that Sproc I get an error that says "The stored procdure executed successfully but did not return any values". And then it gives me an error message that states "Action failed" and gives me back 3 arguements stored procedure name,datasheet, edit. I don't care about this error message I want it to dissappear.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Stored Procedures in Access

    If you are using ADO is to call the Execute method from the Connection object use the adExecuteNoRecords criteria. Here is an example:

    Dim cn as ADODB.connection

    Set cn = new ADODB.connection

    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataBaseName;Data Source=ServerName"

    cn.Open

    cn.Execute "sp_Test", adExecuteNoRecords

    cn.Close

    Set cn = Nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If you are using a querydef, or a pass-through query, then you need to set the ReturnRecords property of the query to FALSE (or no).

  4. #4
    Join Date
    Mar 2004
    Posts
    6

    Re: Stored Procedures in Access

    I need to be more specific. I am using a macro to call the sproc. The sporc asks you for the input variables and then runs. I like that. The SProc works... It updates the Database with the input variables. But then I get that error message I just mentioned.

    Originally posted by SCIROCCO
    If you are using ADO is to call the Execute method from the Connection object use the adExecuteNoRecords criteria. Here is an example:

    Dim cn as ADODB.connection

    Set cn = new ADODB.connection

    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataBaseName;Data Source=ServerName"

    cn.Open

    cn.Execute "sp_Test", adExecuteNoRecords

    cn.Close

    Set cn = Nothing

  5. #5
    Join Date
    Mar 2004
    Posts
    6

    Question

    How do I get to the ReturnRecords property? This is a stored procedure that is on SQL server. I access it by clicking a button that runs a macro that activates the Sproc. Which asks you for variables and then inputs these into the DB.


    Originally posted by PracticalProgram
    If you are using a querydef, or a pass-through query, then you need to set the ReturnRecords property of the query to FALSE (or no).

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What verision of Access are you using ?

  7. #7
    Join Date
    Mar 2004
    Posts
    6
    2000

    Originally posted by PracticalProgram
    What verision of Access are you using ?

  8. #8
    Join Date
    Mar 2004
    Posts
    6
    Screen print of Error message
    Originally posted by PracticalProgram
    What verision of Access are you using ?
    Attached Files Attached Files

  9. #9
    Join Date
    Mar 2004
    Posts
    6
    Originally posted by PracticalProgram
    What verision of Access are you using ?
    Another ScreenPrint
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Stored Procedures in Access

    Yes you can send your variables to the stored-procedures like so:

    Dim cn as ADODB.connection

    Set cn = new ADODB.connection

    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataBaseName;Data Source=ServerName"

    cn.Open

    cn.Execute "sp_Test VARIABLE1, VARIABLE2", adExecuteNoRecords

    cn.Close

    Set cn = Nothing

    With adExecuteNoRecords it does not receive any messages.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •