Solved

Excel VBA to call SQL Stored procedure

Posted on 2015-01-21
12
113 Views
Last Modified: 2015-01-22
I have problem using VBA to call. No error pop up and nothing is returned, Any idea ?


Tks
Result.pdf
0
Comment
Question by:AXISHK
[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
  • 6
  • 5
12 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561452
Shouldn't you define the cmd and Rs variables?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561456
Also try Rs.Open cmd instead of the Execute method.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561458
Sorry but starting to get refreshed with ADO. For long that I don't use it.
You need to specify  that you want to run a Stored Procedure so add the following line:
 Cmd.CommandType = adCmdStoredProc
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:AXISHK
ID: 40561460
Pop up another message ...

actually, I follow the coding on the link below,,
http://mikesknowledgebase.azurewebsites.net/pages/SQLServer/RunStoredProcedureFromExcel.htm

Tks
Result1.png
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 200 total points
ID: 40561462
Hi,

in order to reuse the code for this purpose i prepare the function to open connection:

Private Cn As ADODB.Connection
Public Function ConnectionString() As ADODB.Connection

    Dim connstr As String

 connstr = "Provider=SQLOLEDB;Server=(local);Database=MYDB;Trusted_connection=yes"
    
    If Cn Is Nothing Then
    Set Cn = New ADODB.Connection
        With Cn
        .ConnectionString = connstr
        .CommandTimeout = 0
        .Open
        End With
    End If
    
   Set ConnectionString = Cn
End Function

Open in new window


then i have function to call the procedure with 1 parameter
Private rs As New ADODB.recordset
Private cmd As New ADODB.Command

Public Function sql_query(proc_name As String, param1 As String)
Set Cn = ConnectionString()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    With cmd
        .CommandText = proc_name
        .CommandType = adCmdStoredProc
        .ActiveConnection = Cn
        .Parameters.Refresh
        .Parameters(1).Value = param1
    End With
rs.Open cmd
Set sql_query= rs
End Function

Open in new window



and finally i have sub which will cal the function and display the results on the sheet..

sub display_proc_result

' call of the function
Set rs = sql_query("wbdisplaybook", "P000210584")

'display retrived dataset

Me.Range("A8:P1000").ClearContents
 col = 0
 Row = 8

      Do While Not rs.EOF
  
  If Row = 8 Then
      Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Name
      col = col + 1
    Loop
    End If
    
    Row = Row + 1
    col = 0
    
    Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Value
      col = col + 1
    Loop
  
    rs.MoveNext
  Loop
  
  Me.Range("A8:P1000").EntireRow.Columns.AutoFit
end sub

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40561465
AXISHK, maybe you didn't see my last comment:
You need to specify  that you want to run a Stored Procedure so add the following line:
  Cmd.CommandType = adCmdStoredProc
0
 

Author Comment

by:AXISHK
ID: 40561480
Yes, already added that but still don't retrieve any record...

Public Const ConPro As String = "Provider=sqloledb;Server=10.0.x.x;Database=HKG;Uid=report;Pwd=xxxxl"
Dim conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40561482
Even the commandtype?
What's the error now?
0
 

Author Comment

by:AXISHK
ID: 40561499
I have rewritten the code, it stop at ".Parameter(1),Value = stPar1

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

conn.Open ConPro
cmd.ActiveConnection = conn

Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenStatic
With cmd
    .CommandText = "wbdisplaybook"
    .ActiveConnection = conn
    .Parameters.Refresh
    .Parameters(1).Value = stPar1
End With

Rs.Open cmd
0
 

Author Comment

by:AXISHK
ID: 40562095
Try to use Haris's proposed macro to test the stored proc but it return with error. Any idea ?

Tks
StoredProc.xlsm
0
 

Author Comment

by:AXISHK
ID: 40563766
Find a root problem,

If i take away redirecting to a temporary table and select the records from a table directly, my code works fine.
However, whole store procedure need to use few temporary table in order to get a result. any idea how to solve it ? Tks


CREATE PROCEDURE [dbo].[sp_wbDisplayBook] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.THK_INVENTD2ID, D.THK_INVENTD3ID, D.THK_INVENTD4ID, D.THK_INVENTDGOLDTYPEID, THK_REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END AS KTL_STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
                  +SUBSTRING(A.COSTGROUPID,4,3)+CAST(CAST(SUM(OUTQTY) AS decimal(20,2)) AS NVARCHAR(10)) Else '' END AS Material
 into #TM1
 FROM DynamicsAX_PRO.dbo.THK_LASTWIPMATERIALTRANS A
INNER JOIN DynamicsAX_PRO.dbo.BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPID
INNER JOIN DynamicsAX_PRO.dbo.INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBATCHID AND C.DATAAREAID='OPR'
INNER JOIN DynamicsAX_PRO.dbo.BOMVERSION D ON C.THK_REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
inner join dbo.split(@INVENTBATCHID,',') as e on A.INVENTBATCHID COLLATE DATABASE_DEFAULT = e.Data COLLATE DATABASE_DEFAULT
GROUP BY SUBSTRING(A.COSTGROUPID,4,3),CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END,
A.COSTGROUPID,A.INVENTBATCHID,D.ITEMID,D.THK_INVENTD2ID,D.THK_INVENTD3ID,D.THK_INVENTD4ID,D.THK_INVENTDGOLDTYPEID,
THK_REFBOMID

select * from #TM1
END
0
 

Author Closing Comment

by:AXISHK
ID: 40563886
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

729 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