Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Execute SQL Stored Procedure from VBA

Posted on 2012-03-14
8
Medium Priority
?
435 Views
Last Modified: 2012-08-13
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
Comment
Question by:AkAlan
  • 4
  • 3
8 Comments
 
LVL 36

Accepted Solution

by:
Norie earned 2000 total points
ID: 37721390
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
 
LVL 6

Author Comment

by:AkAlan
ID: 37721423
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
 
LVL 36

Expert Comment

by:Norie
ID: 37721464
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:AkAlan
ID: 37721467
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
 
LVL 36

Expert Comment

by:Norie
ID: 37721546
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 37721977
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
 
LVL 36

Expert Comment

by:Norie
ID: 37722414
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
 
LVL 6

Author Comment

by:AkAlan
ID: 37722430
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question