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 AccessMicrosoft SQL Server
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
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros