Getting a Cannot Find Stored procedure error MS Access

Hello,
I've created stored procedures and views on a test system and am executing the stored procedures via a MS Access front-end.  The code works fine in the development environment, but I get the above error when trying to execute the code in the production environment. I switch between the two environments by specifying a different connection string constant at runtime.

Dev
Public Const CONST_DEV_CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Data Source = USMTCSVSQL01\Vantage;Initial Catalog=mfgtest803;Integrated Security=SSPI;"

Production
Public Const CONST_PROD_CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Data Source = USSALSVANTAGE01;InitialCatalog=mfgsys803;Integrated Security=SSPI;"

I have verified that all the required stored procedures and views were imported from the dev environment into the production environment.  I can also execute the stored procedures via SSMS.    However when I try to execute the stored procedures from within Access, I get an error saying that the stored procedure cannot be found

Option Compare Database

Option Explicit

Public gstrSourceName As String
Public gstrReportName As String
Public gstrType As String
Public gstrComments As String
'Dev Database
Public Const CONST_DEV_CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Data Source = USMTCSVSQL01\Vantage;Initial Catalog=mfgtest803;Integrated Security=SSPI;"
'Production Database
Public Const CONST_PROD_CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Data Source = USSALSVANTAGE01;InitialCatalog=mfgsys803;Integrated Security=SSPI;"
Public Const CONST_FORM_CAPTION = "frmReportMenu"

Open in new window


Option Compare Database
Option Explicit
Private mobjXlApp As Object
Private mobjXLBook As Object
Private rst As ADODB.Recordset
Private strReport As String

Public Sub GenerateReportRecordset(strSProcName As String)

Dim Cnn As New ADODB.Connection
Dim strReportName As String
Dim strConnection As String

' Establish connection.
Set Cnn = New ADODB.Connection
'Cnn.ConnectionString = CONST_DEV_CONNECTION_STRING
Cnn.ConnectionString = CONST_PROD_CONNECTION_STRING
Cnn.ConnectionTimeout = 360
Cnn.Open

Set rst = New ADODB.Recordset
'rst.Open strSProcName, Cnn, adOpenForwardOnly, adLockOptimistic
rst.Open strSProcName, Cnn
strReportName = gstrReportName
Call Create_Report_Workbook(strReportName)
MsgBox "Report Completed", vbOKOnly, "Report Status"

' Process results from recordset, then close it.
Cnn.Close
Set Cnn = Nothing
'rst.Close
Set rst = Nothing


End Sub

Open in new window

Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Have you also checked the permissions on the stored procedure? They are not automatically copied. You may need an explicit GRANT EXECTUE ON yourProcName TO roleOrUserName;
0
Juan VelasquezAuthor Commented:
I'll go ahead and ask IT to verify that I have the explicit grant permissions.
0
Juan VelasquezAuthor Commented:
Hello Ste5an,

I found the problem.  In the production connection string I was missing the space in Initial Catalog.  It was written as InitialCatalog instead of Initial Catalog.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
I was able to find the problem on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.