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.