SQL-Server Stored Procedure called from Access Front-end does nothing!
Over the past 10 years I have developed several applications using SQL Server as the back-end database with Access "projects" (.ADP) as the front-end applications.
Now that the Access ADP Projects are no longer supported by Microsoft I am trying to migrate one of my smaller projects to SQL Server 2016 and Access 2016. So far I have managed to get my "linked tables" to work OK and last week I obtained help "connecting" to the SQL Server database using VBA behind my Access front-end (thanks, Kelvin), but I am still having problems actually executing SQL Server Stored Procedure (SPs).
The SP I am testing this with does a simple INSERT into a SQL Server Table, filling the new record's fields with values sent to the SP as a "parameter" array. I am using an "output" parameter that ultimately gets set to the @@ROWCOUNT value inside the SP and would normally come back as "1". In my tests, I am setting this parameter to value "10" before I send it to the SP, inside the SP I am immediately assingning the value "5" to the parameter before the INSERT instruction, and setting the parameter to the @@ROWCOUNT value immediately after the INSERT command inside the SP.
When I check the returned parameter value back in Access, after the call to the SP, it is still "10" suggesting that it was not changed at all in the SP. Also, there is no sign of a new record in the SQL Server Table, suggesting the SP has not been executed at all.
AS far as I can see from other web-sites I have checked, my mechanism for filling out my parameter array is still valid, and the absence of any run-time error messages suggests that everything is OK at the Access / VBA end of things. Is there a SQL Server (or Access) setting somewhere that governs whether such calls to SQL-Server SPs are allowed from Access ACCDBs?
I can attach some of my code in follow-up postings if there is not a fundamental "simple" step that I am missing.
Many thanks.
Now that the Access ADP Projects are no longer supported by Microsoft I am trying to migrate one of my smaller projects to SQL Server 2016 and Access 2016. So far I have managed to get my "linked tables" to work OK and last week I obtained help "connecting" to the SQL Server database using VBA behind my Access front-end (thanks, Kelvin), but I am still having problems actually executing SQL Server Stored Procedure (SPs).
The SP I am testing this with does a simple INSERT into a SQL Server Table, filling the new record's fields with values sent to the SP as a "parameter" array. I am using an "output" parameter that ultimately gets set to the @@ROWCOUNT value inside the SP and would normally come back as "1". In my tests, I am setting this parameter to value "10" before I send it to the SP, inside the SP I am immediately assingning the value "5" to the parameter before the INSERT instruction, and setting the parameter to the @@ROWCOUNT value immediately after the INSERT command inside the SP.
When I check the returned parameter value back in Access, after the call to the SP, it is still "10" suggesting that it was not changed at all in the SP. Also, there is no sign of a new record in the SQL Server Table, suggesting the SP has not been executed at all.
AS far as I can see from other web-sites I have checked, my mechanism for filling out my parameter array is still valid, and the absence of any run-time error messages suggests that everything is OK at the Access / VBA end of things. Is there a SQL Server (or Access) setting somewhere that governs whether such calls to SQL-Server SPs are allowed from Access ACCDBs?
I can attach some of my code in follow-up postings if there is not a fundamental "simple" step that I am missing.
Many thanks.
Last Comment
ste5an
How do you execute your sproc? Per ADO or DAO pass-through?
ASKER
Thanks for the very prompt response.
I have attached the VBA sub procedure I use at the Access end of things. Basically it uses a "cmdCommand AS ADODB.Command" variable.
The sub procedure is peppered with comments that remind me what I am doing at each stage.
It also contains a local intPosn variable that I use to trace the progress through the procedure in case the "HandleError" message needs to be displayed if it crashes. Up to last week it was crashing at the "cmdCommand.Execute" instruction (intPosn = 120) but after getting help with my "Server Connection String" that now no longer happens. I had hoped that eliminating that problem would have allowed the SP to be run successfully.
I have a couple of variations of this sub procedure in my VBA that I use, depending on whether I need a paramater array and/or expect a recordset to be created by the called SP.
Hope this helps shed some light on my problem.
SP_VBA_CallingProcedure.docx
I have attached the VBA sub procedure I use at the Access end of things. Basically it uses a "cmdCommand AS ADODB.Command" variable.
The sub procedure is peppered with comments that remind me what I am doing at each stage.
It also contains a local intPosn variable that I use to trace the progress through the procedure in case the "HandleError" message needs to be displayed if it crashes. Up to last week it was crashing at the "cmdCommand.Execute" instruction (intPosn = 120) but after getting help with my "Server Connection String" that now no longer happens. I had hoped that eliminating that problem would have allowed the SP to be run successfully.
I have a couple of variations of this sub procedure in my VBA that I use, depending on whether I need a paramater array and/or expect a recordset to be created by the called SP.
Hope this helps shed some light on my problem.
SP_VBA_CallingProcedure.docx
I can attach some of my code in follow-up postings if there is not a fundamental "simple" step that I am missing.Maybe is better. You need to explicitly says that parameter is an OUTPUT parameter.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Now, isn't that strange, and embarassing.
Prior to posting my question this morning I was sure my Table was not having a new record added to it.
While I was posting this morning, I was doing further tests and saw that the new record WAS being inserted into the Table; but I was still getting the wrong value returned from the SP.
Following Vitor Montalvao's reply, I double-checked my earlier step in the process where I fill out my array of parameters in a separate VBA sub procedure. I HAD NOT declared my new testing parameter as an OUTPUT parameter!!!!
I have been doing this sort of programming and testing for 30+ plus years and had copied/pasted without double-checking carefully.
I apologise for wasting your time with an error of my own making.
Thanks very much for the very fast (and humbling) solution.
Prior to posting my question this morning I was sure my Table was not having a new record added to it.
While I was posting this morning, I was doing further tests and saw that the new record WAS being inserted into the Table; but I was still getting the wrong value returned from the SP.
Following Vitor Montalvao's reply, I double-checked my earlier step in the process where I fill out my array of parameters in a separate VBA sub procedure. I HAD NOT declared my new testing parameter as an OUTPUT parameter!!!!
I have been doing this sort of programming and testing for 30+ plus years and had copied/pasted without double-checking carefully.
I apologise for wasting your time with an error of my own making.
Thanks very much for the very fast (and humbling) solution.
Welcome to the club, mate.
I sometimes also feel embarrassed when losing hours around an issue that was originated by a typo or something that I forgot to add.
I guess only happens to who works in the field :)
Cheers
I sometimes also feel embarrassed when losing hours around an issue that was originated by a typo or something that I forgot to add.
I guess only happens to who works in the field :)
Cheers
ASKER
Thanks for your understanding, Vitor.
But here is another strange thing .... having closed the current question I may need to post a new one.
Apart from my own error with my OUTPUT parameter, I can now see why I thought my SP was not executing at all.
When I look at the records of the SQL-Server Table from within my Access front-end, I cannot see any new records that have been added.
I can only see records that were in the Table when I initially linked my Access ACCDB to my back-end SQL Server database.
This morning I was using SQL Server Management Studio to inspect the Table and I can see that my Access front-end has in fact been updating the Table all along, but it just does not look like it from the Access end of things. (My OUTPUT paramater mistake when I was debugging from the Access end just compounded my belief that the SP was not being executed.)
Why is Access still only seeing the contents of the Table as they were when I first linked to it a week or so ago, despite Access actually adding new records?
Should I create a new Experts Exchange question?
Best regards.
But here is another strange thing .... having closed the current question I may need to post a new one.
Apart from my own error with my OUTPUT parameter, I can now see why I thought my SP was not executing at all.
When I look at the records of the SQL-Server Table from within my Access front-end, I cannot see any new records that have been added.
I can only see records that were in the Table when I initially linked my Access ACCDB to my back-end SQL Server database.
This morning I was using SQL Server Management Studio to inspect the Table and I can see that my Access front-end has in fact been updating the Table all along, but it just does not look like it from the Access end of things. (My OUTPUT paramater mistake when I was debugging from the Access end just compounded my belief that the SP was not being executed.)
Why is Access still only seeing the contents of the Table as they were when I first linked to it a week or so ago, despite Access actually adding new records?
Should I create a new Experts Exchange question?
Best regards.
I'm not an Access specialist. In fact, I've worked with Access in the end of 90's so I'm not sure what changed from then until now.
How many SQL Server instances do you have? Might you linked the tables in Access to a DEV SQL Server instance and then you're checking in PROD SQL Server instance so they aren't really the same database?
How many SQL Server instances do you have? Might you linked the tables in Access to a DEV SQL Server instance and then you're checking in PROD SQL Server instance so they aren't really the same database?
ASKER
Thanks again for another prompt response.
Given my recent debugging experience I think I will investigate a bit further (and more carefully) myself first, then post a new question if I am still stumped. I know my Access VBA code still has some references to "CurrentProject.AccessConn
Thanks again.
Given my recent debugging experience I think I will investigate a bit further (and more carefully) myself first, then post a new question if I am still stumped. I know my Access VBA code still has some references to "CurrentProject.AccessConn
Thanks again.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
