I need to call a function to get a value from a sql server procedure

Fordraiders used Ask the Experts™
Access 365
sql server 2008

I'm having issues with a locked sql server table, i think from this small routine. ?

So , i need to try and
I need to call a function to get a value from a sql server procedure.(I currently do not have a function/procedure)
I need to assign that value from sql server to a variable.

Current Access vba code Linked to a sql server table. I would like Sql server to tell me the answer and not VBA.
StrSqlC = "SELECT Max(dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id) AS MaxOfNSC_Id " & _
"FROM dbo_t_nsc_trackcode_assigned_DataEntry" & _
" WHERE (((dbo_t_nsc_trackcode_assigned_DataEntry.ID_Racfid)=  '" & str & "' ));"
             Set r = CurrentDb.OpenRecordset(StrSqlC, dbOpenDynaset, dbSeeChanges)
tp = r.RecordCount
MaxId_csp = r.Fields(0)
Set r = Nothing

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
If you want SQL to tell you then you should use a pass through query to interact directly with SQL Server and get what you want.
The easiest way would be to create a passtrhough query with the same SQL ...point it to the appropriate DSN and just execute it.
What is the fieldtype of field "ID_Racfid"?

How many records in the table?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

what do you mean by "locked SQL Server table", or did you mean "linked SQL Server table"?

I agree with John, I would create a pass-through query and would modify the query's .SQL property to account for your [ID_Racfid] field.  Then I would simply use a DLookup of that query to retrieve the value, something like:
strSQL = ...
currentdb.querydefs("YourQueryName").SQL = strSQL
MaxID_csp = NZ(Dlookup("MaxOfNSC_Id", "YourQueryName")

Open in new window


Used john's suggestion, but also used:
created sp in sql server:
ALTER PROCEDURE [dbo].[sp_NSC_Maxid_DataEntry]
 @racf Nvarchar(10)
select max([NSC_Id]) from [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned_DataEntry]
ID_Racfid = @racf

then in Access:
' ADDED 1/2/2020
strConnect = "provider=sqloledb;Server=PR.cam.gr.com;Database=Program_W;Uid=we;Pwd=zprd;"
' Instantiate the connection object
Set cnn = New ADODB.Connection
' Open the connection based on the strConnect connect string arguments
cnn.Open strConnect

' Instantiate the command object
Set cmd = New ADODB.Command

' Assign the connection and set applicable properties
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
'cmd.CommandText = "sp_NSC_ComputedTime"
cmd.CommandText = "sp_NSC_Maxid_DataEntry"
cmd.CommandTimeout = 6000
' Instantiate the recordset object by using the return value
' of the command's Execute method. Supply the parameters by
' packing them into a variant array
cmd.Parameters.Append cmd.CreateParameter("@racf", adVarChar, adParamInput, 10, strRacfid)

Set rstRF = cmd.Execute

Last_id = rstRF.Fields(0)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial