Sql Server Procedure Access 2010

I have a problem with a call to a sql server stored procedure which works when called from Access 2010 but not from Access 2007.
This is an Access front end looking at a sql server 2014 backend using DNS-less connections for tables, views, pass-thru queries etc.
This is the stored proc:

CREATE PROCEDURE uspGetStockOnOrder
@OnOrder int output,
@StockID int
AS
set nocount on;
SELECT @OnOrder = dbo.vStock_on_order.OnOrder
FROM dbo.vStock_on_order
WHERE dbo.vStock_on_order.Ord_SppSizeID = @StockID;
RETURN
Go
---------
Within Access, the VBA the code is:
Public Function GetStockOnOrder(Param1 As Long, Param2 As Long) As Long
On Error GoTo ConnectError
Dim objConnection As New ADODB.Connection, objCom As ADODB.Command, strConnect As String, DataSrce As String, RetVal As Integer
DataSrce = DLookup("ServerName", "Master_Central", "User_Loc = '" & Pub_UserLoc & "'") ' different users can have different paths to the server
Set objCom = New ADODB.Command
objConnection.Provider = "sqloledb"
strConnect = "Integrated Security=SSPI; Persistent Security info = false; Data Source = " & DataSrce & "; Initial Catalog=CM_BE"
objConnection.Open strConnect
With objCom
.ActiveConnection = objConnection
.CommandType = adCmdStoredProc
.CommandText = "uspGetStockOnOrder"
.Parameters("@StockID").Value = Param1
.Parameters("@OnOrder").Value = Param2
.Execute
GetStockOnOrder = Nz(CLng(.Parameters("@OnOrder").Value)) ' return value
End With

Exit_ConnectError:
Exit Function
ConnectError:
GetStockOnOrder = -1 ' flag to convey error to calling routine to warn user that query fell over.
errornum = Err.Number
If errornum = -2147467259 Then
MsgBox "The back-end database name or path to the server is not valid." & vbCrLf & " DO NOT CONTINUE!", vbCritical, "DataSense Help"
DoCmd.Close acForm, Pub_FormName
Resume Exit_ConnectError
End If
End Function
--------------------
I have tried using a more complete definition of the parameters in the calling routine:
.Parameters.Append .CreateParameter("@StockID", adInteger, adParamInput, 4)
.Parameters("@StockID").Value = Param1
and even
.Parameters.Append .CreateParameter("@StockID", adInteger, adParamInput, 4, Param1)
But this still bombs out when the .Execute statement is reached.

All my other stored procs (table functions, views etc) and pass-thru queries work fine. It is just when passing parameters, and only when using Access 2007.

I would be grateful if someone could point out what I'm doing wrong (or not doing right).
DataSenseConsultant/Data AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

I would be surprised if that works in any other Access version.

You can use two methods to create the parameter collection: Either define the parameters one by one or use the "Refresh" method of the parameters collection (which needs the permission to view the definition of the SP to work). This is easier for the programmer but also cost an extra round-trip to the server for each call .
The other method you already have, but not complete: You have a parameter definition for the StockID variable, but not for the OnOrder variable, which also must be declared as InputOutput. ADO does some things automatically so maybe it works, but it's no guarantee that it gets the right datatypes if you do not explicitly define it or use Refresh. Moreover you should install the SQLNCLI driver which you can download for any SQL Server version and use that instead of sqloledb.

As the call is done by ADO (and also the return of the OUTPUT variable) maybe you have an outdated ADO reference in your VBA, you should check that it is 6.0 or 6.1 for any OS since Vista. In Windows XP it should be 2.8, not higher. If you made the database using an older Windows OS you may need to remove the reference and set it again and recompile the code to be sure you are using the most current ADO version, there was a break beginning with Windows 7.

Cheers,

Christian
DataSenseConsultant/Data AnalystAuthor Commented:
Thanks for feedback. I am learning that there some major changes from Access 2007 to Access 2013.
Actually, I did try with both parameters fully defined - I just mentioned one in my earlier posting to keep the message brief.
Have tried again with Access 2007 (making sure the parameters were fully defined in the calling procedure) i.e.
        .CreateParameter ("@OnOrder")
        .Parameters.Append .CreateParameter("@OnOrder", adInteger, adParamInputOutput, 4, Param2)
        .CreateParameter ("@StockID")
        .Parameters.Append .CreateParameter("@StockID", adInteger, adParamInput, 4, Param1)
and still no success.
ADO references are all at level 6.
And the .refresh commend is not accepted?

As I don't have full control over the client's computers (where the problem occurs), can you give me a little background as to what installing sql native client might do to solve the issue (before I get them to go to the trouble)?

Or perhaps we should just get them to upgrade their computers and install Office 2013?

Thanks in anticipations
BitsqueezerCommented:
Hi,

if the Refresh method do not work you cannot use it. In most cases it is because the SQL Server denied the permission to view the definition of the stored procedure which Refresh needed.
In that case you are forced to create the parameters in the frontend by yourself but you also must change that manually whenever the parameters are changed.

The SQL Server native client is the "most natural" way to work with SQL Server as it fully supports all possibilities of SQL Server. It's a simple driver so it is no problem to install that, doesn't affect any other things on the client computer. As far as I know it is part of Windows since Windows 7, but don't need to be the newest version (I don't think that it is the one for SQL Server 2014, but don't ask me which one, doesn't matter, you can also work with older SQLNCLI versions).
Installation of Office does has no effect on the way ADO or the used driver works, so it seems that you have another problem. It would be good if you could explain what exactly happens and if the client only have the runtime version of Access you should start SQL Server profiler and watch what happens when this procedure is executed as that sometimes offers error messages Access does not show. It can also be wrong parameter values which do not report error messages and so on. I always save the parameters of a SP into an error log in the SP itself to see what happened.

Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

DataSenseConsultant/Data AnalystAuthor Commented:
Hi,  thank for the further comments.
It seem that last night I finally solved the problem. I need to do a bit more testing to be confident but  the solution was adding VB references to Microsoft ActiveX Data Objects 6.1, Microsoft Data Access Components Installed Version, and Microsoft ActiveX Data Objects Recordset 6.0 library (which was previously at 2.8).
This part of VB programming is not my strong point - so I need to explore it further and determine which one did the trick.
I note you made a remark about the ADO reference in your previous comment, but as I was not using Vista I did not give that much more attention, till last night.
So thanks.
DataSenseConsultant/Data AnalystAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for DataSense's comment #a40778265

for the following reason:

Comments prompted me to look further into the matter which lead to me finding a solution
BitsqueezerCommented:
Hi,

so it looks to me as if I told you the right solution, isn't it?

Cheers,

Christian
DataSenseConsultant/Data AnalystAuthor Commented:
Yes,  thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.