Calling Stored Procedure While Passing Parameters from Access Module in Vba

Calling Stored Procedure while passing parameters from Access Module in VBA

Dim conn As ADODB.Connection 
Dim cmd As ADODB.Command 

Set conn = New ADODB.Connection 
conn.ConnectionString = “your connection String here” 
conn.Open 

Set cmd = New ADODB.Command 
cmd.ActiveConnection = conn 
cmd.CommandType = adCmdStoredProc 
cmd.CommandText = "upInsertToInstrumentInterfaceLog" 

cmd.parameters.Append cmd.CreateParameter("@BatchID", adVarChar, adParamInput, 60, "value for BatchID")   
cmd.parameters.Append cmd.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, "value for InstrumentName")   
'...

cmd.Execute 
conn.Close 

Calling stored procedure using VBA

Victoria,

You can run a stored procedure using ADO, like below...

Set mobjConn = New ADODB.Connection
mobjConn.Open "your connection string"
Set mobjCmd = New ADODB.Command
With mobjCmd
    .ActiveConnection = mobjConn 
    .CommandText = "your stored procedure"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Append .CreateParameter("your parameter name", adInteger, adParamInput, , your parameter value)
    ' repeat as many times as you have parameters

    .Execute
End With

To get your connection string, you can use the line

Debug.Print CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect

in the Immediate Window and that should show you a connection string which you can use.

Would you try that and let me know if you have any problems.

Ash

Execute MySQL stored procedure with parameters in Access frontend via VBA

Consider using ADO for a parameterized query to call your stored procedure. Currently, you are using DAO (Access' default database API) to access a pass-thru query (a saved Access querydef). However, this type of query does not see anything in the frontend, only the backend RDMS particularly the MySQL SQL dialect and its connected database objects. Hence, you cannot bind local parameter values to it. And PARAMETERS clause is only part of the Access SQL dialect and will fail MySQL syntax.

MySQL Stored Procedure

CREATE PROCEDURE `mystoredproc`(IN param VARCHAR(254))
BEGIN
   SELECT * FROM table WHERE field=param;
END

ADO Parameterized Query

Public Sub CallMySQLProc()    
    Dim conn As Object, cmd As Object, rst As Object
    Const adCmdStoredProc = 4, adParamInput = 1, adVarChar = 200

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' DSN-LESS CONNECTION
    conn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};host=hostname;database=databasename;" _
                & "UID=username;PWD=****"

    ' CONFIGURE ADO COMMAND
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = "mystoredproc"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 15
    End With

    ' APPEND NAMED PARAM
    cmd.Parameters.Append cmd.CreateParameter("param", adVarChar, _
                                              adParamInput, 254, "some.name@example.com")
    Set rst = cmd.Execute

    ' FREE RESOURCES
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set conn = Nothing

End Sub

---

DAO Dynamic Pass-Through Query

Here, you can build the querydef's .SQL statement dynamically, but will not be parameterized:

Private Sub ParametricQuery()
  Dim QDef As QueryDef
  Dim Result As Recordset

  Set QDef = CurrentDb.QueryDefs("PassThruQ")
  QDef.SQL = "CALL mystoredproc('some.name@example.com')"

  Set Result = QDef.OpenRecordset

  Do While Not Result.EOF
    Debug.Print Result.Fields(1)    'Print to immediate window a field from the results
  Loop

  Result.close
  Set Result = Nothing
  Set QDef = Nothing
End Sub

Excel VBA Use ADO Command Object to Call Stored Query in Access

adCmdStoredProc doesn't seem to be designed for MsAccess queries. Change it to adCmdTable. Also, when setting the parameter values they are indexed from zero.

 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim cmd As New ADODB.Command

 Set conn = makeConnection()
 cmd.CommandText = "accessLevelByUN"
 cmd.CommandType = adCmdTable

 cmd.ActiveConnection = conn

 cmd.Parameters.Refresh

 MsgBox (cmd.Parameters.Count)

 cmd.Parameters(0) = userName

 Set rs = cmd.Execute
...

VBA - How to call a module with arguments from Application.Run?

The correct version of your second example is

Application.Run "regCOTSImporter.regressionTestCOTS", foldername

Therefore, you can simply use

Application.run test1 & "." test2, foldername

for the general case, as test1 & "." test2 is already a string.

My vba code is designed return a value from a stored procedure (in SQL) using an output parameter, but I am unable to get a value to return

I figured it out. For anyone who is curious whenever you are looking to return a single value from a database within SQL from vba don't use a recordset. (although I guess you can) The reason is Recordsets are designed to return data in the form of a table - which caused a lot problems for me.

To return a single value all you have to do is execute your SQL command in vba, and then set your return variable equal to the value of the output parameter from you're stored procedure. If that doesn't make sense I have put all my working code below from vba and my stored procedure.

TEST SUB - I used this to test one example of possible inputs from excel.

Private Sub TestSub()

SQL_StoredProcedure "Stainless Steel", "DENSITY", "WIRE_TYPE", "WIRE_INDEX"

End Sub

VBA MODULE

Option Explicit

Private output As String

' Parameters are passed in that will be used to create a SQL statement
' for a stored procedure.
Public Sub SQL_StoredProcedure(ByVal sql_ui As String, _
                                ByVal sql_pci As String, _
                                ByVal sql_sci As String, _
                                ByVal sql_dtn As String)

    On Error GoTo RunTimeError

    Dim sqlconxn As ADODB.connection ' Connection between vba and SQL
    Dim sqlcmd As ADODB.Command ' Operates as a command between SQL and vba
    Dim sqlfld As ADODB.field ' Used to refer to the records or fields in SQL

    Dim output As String ' The output retrieved from the SQL stored procedure
    Dim conxnString As String ' Connection string is used to make the connection between vba and SSMS

    Application.ScreenUpdating = False

    ' String used to establish a connection to the database
    conxnString = '{your connection string}'

    ' Every time the SQL_StoredProcedure function is called a new instance of the
    ' connection, command, and recordset are made and then immediately closed once the function finishes
    Set sqlconxn = New ADODB.connection
    Set sqlcmd = New ADODB.Command

    sqlconxn.ConnectionTimeout = 30
    sqlconxn.Open conxnString ' makes the connection between SQL

    MsgBox "Connection 1 state: " & GetState(sqlconxn.state) ' Checks the status of the connection

    sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
    sqlcmd.CommandText = "GET_ADDITIVE_DATA" ' name of the stored procedure
    sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection

    ' Parameters that need to be called and defined whenever the stored procedure is called
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@user_index", adVarChar, adParamInput, 255, sql_ui)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@primary_col_index", adVarChar, adParamInput, 255, sql_pci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@secondary_col_index", adVarChar, adParamInput, 255, sql_sci)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255, sql_dtn)
    sqlcmd.Parameters.Append _
        sqlcmd.CreateParameter("@record_value", adVarChar, adParamOutput, 255)

    ' Executes the sql command with all parameters already passed in
    sqlcmd.Execute

    ' output string is set equal to the output parameter of the stored procedure
    output = sqlcmd.Parameters("@record_value").Value

    Debug.Print "OUTPUT: " & output ' prints whatever was returned from the SP

    sqlconxn.Close ' Closes the sqlconxn

    Exit Sub

RunTimeError: ' Reportd any errors that might occur in the system and

    Dim strError As String
    strError = "ERROR: " & Err.Number & vbCrLf & Err.Description
    MsgBox strError
    Debug.Print strError

Exit Sub

End Sub

CHECK STATE FUNCTION - this was just connection confirmation

Private Function GetState(state As Integer) As String

    Select Case state
        Case adStateClosed
            GetState = "Closed"
        Case adStateOpen
            GetState = "Open"

    End Select

End Function

SQL STORED PROCEDURE

USE AFCD
GO

ALTER PROCEDURE dbo.GET_ADDITIVE_DATA (@user_index nvarchar(max),
                                       @primary_col_index nvarchar(max),
                                       @secondary_col_index nvarchar(max), 
                                       @data_table_name nvarchar(max),
                                       @record_value nvarchar(max) output
                                       ) AS
BEGIN

    DECLARE @output_value nvarchar(max)
    DECLARE @query nvarchar(max)

    SET @query = 'SELECT @record_value = ' + @primary_col_index  + 
                 ' FROM ' + @data_table_name +
                 ' WHERE ' + @secondary_col_index + ' = ''' + @user_index + ''''

    EXEC sp_executesql @query, 
                       N'@record_value nvarchar(max) output', 
                       @record_value = @record_value output

END

OUTPUT

OUTPUT: 0.284

---

Related Topics