Solved

Execute SQL Stored Procedure from VBA

Posted on 2012-03-14
8
429 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 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

622 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