August 1, 2004 at 11:54 am
Suppose I have a SQL server function (that returns varchar, or boolean), is there a way to run that function through .adp VBA? (i.e. input parameters from an .adp form, then set a VBA variable to the SQL server function return value)
August 4, 2004 at 8:00 am
This was removed by the editor as SPAM
August 6, 2004 at 9:45 am
Sure, do it all the time. Here is one example of a Sub that takes an ID (record key) and performs a function in a stored procedure Not returning a recordset.
declare adoCNN as adodb.connection
declare adocmd as adodb.command
declare prm as adodb.parameters
lId = 100
call adoexecuteid "storedprocedurename", lId, lReturn
if lReturn = 0 then msgbox "OK"
Public Sub AdoExecuteID(sStoredProc As String, lid As Long, lReturn As Long)
On Error GoTo ErrorTrap
set adocnn = application.currentproject.connection
Set adoCmd = New ADODB.Command
Set prm = New ADODB.Parameter
With adoCmd
.ActiveConnection = adoCnn
.CommandText = sStoredProc
.CommandType = adCmdStoredProc
' Set up a return parameter.
Set prm = .CreateParameter("Return", adInteger, adParamReturnValue)
.Parameters.Append prm
' Set up an input parameter.
Set prm = .CreateParameter("Id", adInteger, adParamInput, , lid)
.Parameters.Append prm
.Execute , , adExecuteNoRecords
lReturn = .Parameters("Return")
End With
Set adoCmd = Nothing
Set prm = Nothing
ExitMe:
Exit Sub
ErrorTrap:
For Each adoError In adoCnn.Errors
Debug.Print adoError.Description, adoError.Number
Next
Stop
Resume ExitMe
End Sub
August 12, 2004 at 3:15 pm
Is this the best way to get a return value from a function? Doesn't this require me to write a procedure to execute the function first? (kind of a roundabout method) I couldn't the above code to work for me, as I kept getting errors related to the parameters...could you show me the T-sql of the procedure this is supposed to work with? (that should enlighten me)
The method I have been using creates a 1 record recordset to return function values, for example:
Public Function fncTest(strIn As String) As Boolean
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
sql1 = "Select Is_Member('" & strIn & "')"
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic
fncTest = Nz(rs(0), False)
rs.Close
Set rs = Nothing
End Function
Is this an inefficient way to perform such a task? Any feedback would be greatly appreciated.
August 13, 2004 at 9:08 am
aTSQL example would be:
create procedure up_Count
@Id int
as
set nocount on
return (select count(*) from CustomerAddress where customerID = @ID)
August 26, 2004 at 5:42 am
'-------------------------------------------------------------------------------------------
Public Function fncTest(strIn As String) As Boolean
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
sql1 = "Select Is_Member('" & strIn & "')"
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic
fncTest = Nz(rs(0), False)
rs.Close
Set rs = Nothing
End Function
'-------------------------------------------------------------------------------------------
I had end-user access rights/permissions problem with code such as yours (It only worked when when I was logged in as dbo)
Your above function would translate as a proper ADP/ADO SQL Function/Stored Procedure call:
'-------------------------------------------------------------------------------------------
Private Sub MyFunction(strIn As String) As Boolean
On Error GoTo Err_MyFunction
MyFunction = False
'----------------------------------------------------------------------------------
'ADO 2.6 - Works only if the parameters are same order as the SQL SProcedure
'----------------------------------------------------------------------------------
Dim cnxCurrent As ADODB.Connection
Set cnxCurrent = CurrentProject.Connection
Dim cmdSProcedureEndUser As ADODB.Command
Dim parInputParameter1 As ADODB.Parameter
Dim parOutputParameter1 As ADODB.Parameter
Set cmdSProcedureEndUser = New ADODB.Command
With cmdSProcedureEndUser
.ActiveConnection = cnxCurrent
.CommandText = "dbo.Is_Member"
.CommandType = adCmdStoredProc
Set parInputParameter1 = _
.CreateParameter("@vcMySQLinputParameterName", _
adVarChar, adParamInput, 123, strIn)
.Parameters.Append parInputParameter1
Set parOutputParameter1 = _
.CreateParameter("@bitMySQLoutputParameterName", _
adBoolean, adParamReturnValue, , 0)
.Parameters.Append parOutputParameter1
.Execute 'on error jumps to Err_MyFunction
End With
'This code is never used as long as the SQL Function/Stored Procedure generates
'~~~~~~~~~~~~~~~~~~~~~~~ an error which is processed by Err_MyFunction
If parOutputParameter1.Value = 1
MyFunction = True
End If
Set cnxCurrent = Nothing
Set cmdSProcedureEndUser = Nothing
Exit_MyFunction:
Exit Sub
Err_MyFunction:
MsgBox "Warning: Error " & Err.Number _
& " whilst checking Is_Member." & vbCrLf & vbCrLf _
& Err.Description, vbExclamation, _
Me.Name & ": Checking if is a member"
Resume Exit_MyFunction
End Sub
'-------------------------------------------------------------------------------------------
Replace the above @... SQL function parameter names and the 123 length with the correct values ...
Above is untested refurbished code from my own Access 2002/ADP/ADO/SQL Server 2000 ...
Alain
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply