How to execute Sybase function/stored procedure from MS-Access vba?

Question

I have a Access front end that has an ODBC connection titled fna to the Sybase back end on a server. I can easily reference the tables in the Sybase database but in the vba I need to be able to call stored procedures and functions that are in Sybase.

Function name is CalcStats.

Edit: After reading through some of the links in the comment, I have gotten to the following code which seems to run but I can't figure out how to find the return value the function is providing.

Dim trash As String
Dim conn As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rs1 As Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "datasource=ODBC;DSN=fna"
conn.Open

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = conn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "CalcStats"

cmd1.Execute

Edit 2: Finally figured out the output part. Thank you A.S.H for the links and Gord Thompson for the method for getting the connection string that for the ADODB that is needed.

So I can now create a connection, trigger the function and get the return value. However, now I'm working on the last part which is adding input parameters. I've added an input parameter to the function in sybase and saved it but I can't seem to pass an input value into the function on the Access side.

Dim trash As String
Dim conn As ADODB.Connection
Dim cmd1 As ADODB.Command

Set conn = New ADODB.Connection
conn.ConnectionString = "datasource=ODBC;DSN=fna"
conn.Open

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = conn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "CalcStats"
cmd1.Parameters.Append cmd1.CreateParameter("MeasurementClm", adInteger, adParamInput, , 3)
cmd1.Parameters.Append cmd1.CreateParameter("OutputValue1", adInteger, adParamReturnValue)

cmd1.Execute

trash = cmd1.Parameters.Item(0).Value
conn.Close

This might help: http://stackoverflow.com/questions/24267080/calling-stored-procedure-vba — A.S.H, Oct 14 '15 at 15:49
https://support.office.com/en-us/article/Process-SQL-on-a-database-server-by-using-a-pass-through-query-b775ac23-8a6b-49b2-82e2-6dac62532a42 — Gord Thompson, Oct 14 '15 at 16:42

score 0 · Accepted Answer · answered Oct 15 '15 at 01:39

So the last problem I was having is that for whatever reason, I could declare an output parameter if there were no inputs but if there are inputs I had to remove the output parameter declaration. The Function output is in the parameters.item(0) position. Thanks to A.S.H and Gord Thompson for the links and suggestions. Here is the code I ended up with that is working now:

Dim trash As String
Dim conn As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rs1 As Recordset

Set conn = New ADODB.Connection
conn.ConnectionString = "datasource=ODBC;DSN=fna"
conn.Open

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = conn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "CalcStats"
cmd1.Parameters.Refresh
cmd1.Parameters.Append cmd1.CreateParameter("InputValue1", adInteger, adParamInput, , 3)
'cmd1.Parameters.Append cmd1.CreateParameter("OutputValue3", adInteger, adParamReturnValue)

cmd1.Execute

trash = cmd1.Parameters.Item(0).Value
conn.Close

How to execute Sybase function/stored procedure from MS-Access vba?

1 Answers1