Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Execute SQL Stored Procedure from VBA

Posted on 2012-03-14
8
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 34

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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 34

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 34

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

715 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