troubleshooting Question

SQL-Server Stored Procedure called from Access Front-end does nothing!

Avatar of colinasad
colinasadFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft Access
9 Comments1 Solution328 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004