?
Solved

Execute SQL Procedures using Excel VBA

Posted on 2003-03-10
10
Medium Priority
?
2,587 Views
Last Modified: 2007-12-19
Hello,

I have no problem constructing sql queries using VBA in Excel. But for some odd reason I can´t seem to find the solution for executing a SQL Procedure using Excel VBA.

Thank You in advance
0
Comment
Question by:Claudia_V
[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
10 Comments
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8105345
Claudia, it depends on how you want to do it.  The simplest way is probaby by passing a string for an SQL statement, such as:

"EXEC your_stored_procedure @Paramter1 = 1"

This would execute a stored procedure with 1 parameter.  You would just pass it to your connection, such as:

yourConnection.Execute(your_sql_String)


You could also make an ADODB Command object:

Dim cmd1 as New ADODB.Command
cmd1.Type = adCmdStoredProc
cmd1.ActiveConnection = your_Connection
cmd1.CommandText = your_stored_procedures_name
cmd1.Parameters("your_parameter_name") = 1
cmd1.execute


0
 

Author Comment

by:Claudia_V
ID: 8105806
Hello,

Thank you for replying although my problem persists because it looks like the method for defining connections using the Add(Connection:=...) function (which is the one I use for select Queries in VBA) is a little diferente with the Execute function. What am I doing wrong??? I´ve tried using OpenDatabase and OpenConnection with no luck.

Thanks for the help
0
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8105989
Are you using ADO?  Here is a total sample that one could work with:


'Create ADO Connection
Dim Con1 as New ADODB.Connection
Con1.Connectionstring = "Connection_String"
Con1.Open

'Create a Recordset
Dim Rst1 as new ADODB.Recordset

'Create a Command for your Stored Proc
Dim cmd1 as New ADODB.Command
cmd1.Type = adCmdStoredProc
cmd1.ActiveConnection = your_Connection
cmd1.CommandText = your_stored_procedures_name
cmd1.Parameters("your_parameter_name") = 1

'Display your Results
Set Rst1 = cmd1.Execute
Rst1.MoveFirst
Range("A1").Value = Rst1![Field1]
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.

 

Author Comment

by:Claudia_V
ID: 8109607
Thanks once again.I didn´t try creating an ADO Connection because there I seem to missing an Important Library, because he doesn´t recognise commands such as ".Type". I´ve tried going too the references and adding a few libraries, but I must not be choosing the correct one. Do you know wich one it is??
0
 

Author Comment

by:Claudia_V
ID: 8109795
Just one more peace of information. I already have 2 libraries which I think are important: "Microsoft DAO 3.6 Object Library" and "Microsoft ActiveX Data Object 2.6 Library". Am I missing something???
0
 
LVL 3

Expert Comment

by:RiverGuy
ID: 8112160
Your using DAO, thats why.  The 2.6 ActiveX Data Object (ADO) would be the one you want.  You might have to uncheck the DAO.  DAO is more or less for desktop databases, and you're using SQL Server.  

If I was you, I would make a copy of your excel workbook, so you still have your old one that you had some things working with the DAO.  On the new copy, unreference the DAO, and make sure you have reference the newest version of the ActiveX Data Object Library.  You might even have 2.7.  If thats the case, uncheck the 2.6 and check the 2.7.  Then try the code for the stored procedures.
0
 

Author Comment

by:Claudia_V
ID: 8112382
Thanks once again. I´m sorry to tell you that your sugestion still didn´t resolve my problem (Weird??). In the meanwhile I´ve fortunatly been able to solve my problem without using ADO Commands.

Here goes my solution:
 
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=XXX;DATABASE=XXX;Trusted_Connection=Yes", Destination:=Range("a1"))
        .CommandText = "EXECUTE proc" & " " & "SELECT * FROM tableXPTO"
        .Refresh BackgroundQuery:=False
  End With

Probably it´s basic stuff for you, but my error was not realizing that by using this method I had to have output data for my destination cells(and this method doesn´t work without the destination command!). In my .Commandtext I would just Execute the procedure and not have a command for output. This method, although Im sure is not the best, sutes me just fine because I need to import to excel the table that results from my procedure run in SQL.

Thanks anyway for the help. In the meanwhile if you have any other sugestions (because I wouldn´t mind start bulding ADO Commands) I would be gratefull.

0
 

Expert Comment

by:CleanupPing
ID: 9276025
Claudia_V:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11092497
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11135980
PAQed, with points refunded (20)

Computer101
E-E Admin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

764 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