miral13
asked on
DoCmd.OpenStoredProcedure can't find the object
In an Access database project form I have this little piece of code
DoCmd.SetWarnings False
DoCmd.Close acForm, "View_Edit_Tree"
DoCmd.SetWarnings True
DoEvents
DoCmd.OpenStoredProcedure "dbo.sp_Search_All"
DoCmd.Maximize
And at runtime the following error is returned "can't find the object 'dbo.sp_Search_All' ", error number 7874
The stored procedure exists in the database and expects one parameter.
Can you tell me why it doesn't see/execute it?
Thank you
DoCmd.SetWarnings False
DoCmd.Close acForm, "View_Edit_Tree"
DoCmd.SetWarnings True
DoEvents
DoCmd.OpenStoredProcedure "dbo.sp_Search_All"
DoCmd.Maximize
And at runtime the following error is returned "can't find the object 'dbo.sp_Search_All' ", error number 7874
The stored procedure exists in the database and expects one parameter.
Can you tell me why it doesn't see/execute it?
Thank you
Maybe the owner of the stored procedure is not dbo?
The recommended method is to create a form with the stored procedure as record source. Same with openview, opentable etc.
The recommended method is to create a form with the stored procedure as record source. Same with openview, opentable etc.
miral13,
You failed to respond to 2 Expert comments, either of which provided alternatives to your current issue (and vadimrapp1's comment was most likely your answer).
Did you solve this issue? If so, then please post your solution.
Did you attempt to close this question so that you could avoid the account lockout policy in regards to abandoned questions? I see that you've posted a new question just today.
You failed to respond to 2 Expert comments, either of which provided alternatives to your current issue (and vadimrapp1's comment was most likely your answer).
Did you solve this issue? If so, then please post your solution.
Did you attempt to close this question so that you could avoid the account lockout policy in regards to abandoned questions? I see that you've posted a new question just today.
ASKER
The issue was solved by refreshing the view.
ASKER
The stored procedure in the code above was calling a view. After the view was refreshed everything worked. I hope it is clear.
>> After the view was refreshed everything worked.<<
Could explain how the VIEW was "refreshed"? You can re-compile a Stored Procedure, but I have never heard of refreshing a VIEW, at least in the context you describe.
Could explain how the VIEW was "refreshed"? You can re-compile a Stored Procedure, but I have never heard of refreshing a VIEW, at least in the context you describe.
He refreshed the database view in Access, so Access learned about new view that was created outside of it in the database.
Sometimes people resolve their own questions by hitting F5, scrolling down the page to see what was down there, restarting the computer, etc. - works better than all us experts combined, once they finally get themselves together to do it (after posting the question and having some discussion).
Sometimes people resolve their own questions by hitting F5, scrolling down the page to see what was down there, restarting the computer, etc. - works better than all us experts combined, once they finally get themselves together to do it (after posting the question and having some discussion).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<works better than all us experts combined>
How true!
miral13, glad you reached a solution but in the future please post a short comment if you're deleteing your question, and please respond to Expert comments, even if it's to say that you've solved the issue.
IMO you should PAQ this question by accepting your own answer here: http:#25512766. This would be something that someone else might be able to use one day. If you PAQ and accept your own comment, your points will be refunded to you.
If you'd like to do that, then click the Object button, and then choose your own solution as the Answer and enter something like "found my own solution" in the Reason. This will close out the question in a few days, and keep that question in the PAQ.
How true!
miral13, glad you reached a solution but in the future please post a short comment if you're deleteing your question, and please respond to Expert comments, even if it's to say that you've solved the issue.
IMO you should PAQ this question by accepting your own answer here: http:#25512766. This would be something that someone else might be able to use one day. If you PAQ and accept your own comment, your points will be refunded to you.
If you'd like to do that, then click the Object button, and then choose your own solution as the Answer and enter something like "found my own solution" in the Reason. This will close out the question in a few days, and keep that question in the PAQ.
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.sp_SearchAll"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(0).Value = "Your Value Here"
.Execute
Set .ActiveConnection = Nothing
End With
Set cmd = Nothing