Solved

Excel VBA to call SQL Stored procedure

Posted on 2015-01-21
12
99 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
  • 6
  • 5
12 Comments
 
LVL 46

Expert Comment

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

Expert Comment

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

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
 

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 46

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 46

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

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.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now