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 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 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy