Rem Name of the PROCEDURE Dim strProc : strProc ="POPULATE_EMP_DATA" Rem Name of the DB SCHEMA Dim strSchema : strSchema = "bhive_report" '########## TARGET DB DSN Dim TargetDSN : TargetDSN = "devDSN" Dim TargetDBuser : TargetDBuser = "devUSER" Dim TargetDBpwd : TargetDBpwd = "devPASS" Call runPROC(TargetDSN,TargetDBuser,TargetDBpwd,strProc,strSchema) Rem Sub Routine to Execute Stored Procedure Rem Generic accepts DSN name, username, password and procedure name. Sub runPROC(vDSN,vUser,vPass,vProc,vSchema) On Error Resume Next Const adCmdStoredProc = 4 Dim strTargetConnection : strTargetConnection = "DSN=" & vDSN & ";uid=" & vUSER & ";pwd=" & vPASS&";" Set objConn = CreateObject("ADODB.Connection") objConn.Open strTargetConnection : Rem [ opening Target ORACLE connection ] Set CmdStoredProc = CreateObject("ADODB.Command") With CmdStoredProc Set .ActiveConnection=objConn .CommandText= vSchema & "." & vProc .CommandType=adCmdStoredProc Set rsProc = .Execute If Err.Number 0 then msgbox "[ERROR]" & Err.Description Else msgbox "Executed Procedure Succesfully - " & .CommandText End If End With End Sub
Advertisements