• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Execute SQL Stored Procedure from VBA

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
0
AkAlan
Asked:
AkAlan
  • 4
  • 3
1 Solution
 
NorieVBA ExpertCommented:
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.
0
 
AkAlanAuthor 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
0
 
NorieVBA ExpertCommented:
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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
AkAlanAuthor 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.
0
 
NorieVBA ExpertCommented:
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.
0
 
jmoss111Commented:
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
0
 
NorieVBA ExpertCommented:
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.
0
 
AkAlanAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now