We help IT Professionals succeed at work.

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

319 Views
Last Modified: 2017-03-28
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.
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
How do you execute your sproc? Per ADO or DAO pass-through?

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?

Author

Commented:
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.AccessConnection" behind the scenes, and this might be causing some confusion. It ceratinly looks like I am connecting to the correct database when I call my SP from within Access, but there is possibly something else I am getting wrong with my more general Table Linking.
Thanks again.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.