Excel Vba Stored Prosedur ile Raporlama

ERP / CRM GENEL

ÖMER ÇARNAÇAR

27.02.2018

0 Yorum


Merhabalar ,

Özelikle ERP uygulamalarının raporlama konusunda esnekliği ile rakipi bulunmayan Excel ürünü için VBA tarafında güvenlikli çözümü  örnek VBA kodunu paylasıyorum.

Faydalı olması dileğiyle

 

Sub CallStoredProcedure()

    Dim Conn As ADODB.Connection, RecordSet As ADODB.RecordSet

    Dim Command As ADODB.Command
    Dim ConnectionString As String, StoredProcName As String
    Dim PARAM1 As ADODB.Parameter, PARAM2 As ADODB.Parameter
    
    Application.ScreenUpdating = False
    
    Set Conn = New ADODB.Connection
    Set RecordSet = New ADODB.RecordSet
    Set Command = New ADODB.Command
    
    ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=  '  '  ;INITIAL CATALOG=  ' DATABSE NAME'  ; User Id= 'SQL USER'; Password= 'PASS';"
    
    On Error GoTo CloseConnection
    
    Conn.Open ConnectionString

    StoredProcName = "PROSEDUR NAME"

    With Command
        .ActiveConnection = Conn
        .CommandType = adCmdStoredProc
        .CommandText = StoredProcName
    End With
    
    Set PARAM1 = Command.CreateParameter("PARAM1", adInteger, adParamInput, 100, 151)

    Command.Parameters.Append PARAM1
    
      'Debug.Print OPE_ID

    Set PARAM2 = Command.CreateParameter("PARAM2", adInteger, adParamInput, , "23")
  Command.Parameters.Append PARAM2
    
    Set RecordSet = Command.Execute
    
    Sheets("Sayfa1").Range("A1").CopyFromRecordset RecordSet
    
    RecordSet.Close
    Conn.Close
    On Error GoTo 0
    Application.ScreenUpdating = True
    Exit Sub
    
CloseConnection:
    Application.ScreenUpdating = True
    MsgBox "SQL Stored Procedure Did Not Execute Sucessfully!", vbCritical, "SQL Error"
    Conn.Close

End Sub
 

Başlık Değer