We help IT Professionals succeed at work.

Execute SQL Stored Procedure from VBA

AkAlan
AkAlan asked
on
I am currently using a Command to execute a delete Stored Procedure like so:

 Dim cmd As New ADODB.Command
            Dim shippingLineItemID As Integer
            shippingLineItemID = Me.shippingLineItemID
            With cmd
                .ActiveConnection = CurrentProject.Connection
                .CommandType = adCmdStoredProc
                .CommandText = "spDeleteLOG_ShippingLI"
                .Parameters("@ShippingLineItemId") = shippingLineItemID
                .Execute
            End With

Is there a simpler way to  to just use something like this:

EXEC  spDeleteLOG_ShippingLI shippingLineItemID
Comment
Watch Question

Analyst Assistant
CERTIFIED EXPERT
Commented:
Basically no.

Though I suppose you could put the code you have into a function that takes the shippingLineItemID as an argument and returns the result of the procedure.

Then you could call that function whenever required.

Author

Commented:
Hi imnorie, Thanks for the quick reply, I'm happy to keep using the Command, I was just thinking there was a simpler way to execute it like I do sometimes when dynamically assinging a Forms recordsource like this.


Dim inputParams as integer
inputParams = 2
 Me.RecordSource = "EXEC dbo.spSelectLOG_ShippingLineItems " & inputParams
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Oops, I didn't realise you were dealing with a form and the stored procedure returned a recordset.

I think you can populate a form using a stored procedure but you would still need to run that code to get the recordset and then set the RecordSource at runtime.

Author

Commented:
No I am not looking to poppulate a form, I have a Delete stored Procedure I want to execute from code and I am currently using a Command to execute it. Just wanted to see if there was a cleaner way like when I use  EXEC.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Sorry, my mistake again.

You can definitely set up a pass through query to execute the stored procedure.

However if a parameter is required you still need code and from what I've seen that involves creating a querydef 'on the fly' and executing that.

Commented:
attached .mdb has a good simple example of passing form params to a pass  through querydef. i turned off the form load so shift double click isnt necessary
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
I created a pass-through query with this SQL and with the ODBC connection pointing towards the server/database the stored procedure MyStoreProcedure is in.

EXEC MyStoreProcedure [X]

MyStoreProcedure deletes records from a table where the ID is the value of the parameter passed to it.

I then created this function.
Function RunSP(ID As Long)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strOldSQL As String

    Set db = CurrentDb
    
    Set qdf = db.QueryDefs("StoredProc")
    strOldSQL = qdf.SQL
    
    qdf.SQL = Replace(qdf.SQL, "[X]", ID)
    
    qdf.Execute
    
    
    qdf.SQL = strOldSQL
    
End Function

Open in new window

It replaces the [X] in the query with the ID argument passed to it, executes the query and then replaces the original SQL in the query.

It works by simply calling it like this.
Call RunSP(4)

Open in new window


PS Sorry about the lame, misspelled name for the stored procedure. I think it was the first one I ever wrote so I just keep on re-using it for simple examples, testing etc.

Author

Commented:
Thanks for the replies, I forgot to mention that I am using a project, "adp" not an mdb. From the looks of things, I would say that the ADODB  Command is the simplest way to execute the Delete sproc.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.