• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• 1-vb connect to ORACLE db 2-the name of the DSN is IWHS u can creat your own one!

1-vb connect to ORACLE db 2-the name of the DSN is IWHS u can creat your own one!

1-vb connect to ORACLE db 2-the name of the DSN is "IWHS" u can creat your own one!

Rate 1-vb connect to ORACLE db 2-the name of the DSN is IWHS u can creat your own one!

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'*  Name        : OraOpnConnection
'*  Author      : Sylvain Guérin, 14-02-2003
'*  Purpose     : ATTENTION lorsque tu fait un call à oracle avec un SQL exemple:
'*                MyDB.Execute "truncate table nom_nmdt", dbSQLPassThrough
'*                Important de ne pas oublier ", dbSQLPassThrough" à la fin du Execute
'*                permet à ORACLE d'identifier quel type de requete il est fait.

Public Sub OraOpnConnection()
 
On Error GoTo Err_OraOpnConnection
   
   ' connection de type ADO sur une base de donnée ORACLE
   Set MyDB = Workspaces(0).OpenDatabase("", True, False, "ODBC;DSN=IWHS;USR=BLC_DSS;PWD=BLC_DSS")
    
Exit_OraOpnConnection:
   Exit Sub

Err_OraOpnConnection:
    MsgBox "2000 erreure de procédure", vbCritical, "PROC: OraOpnConnection, autres"
    Resume Exit_OraOpnConnection
 
End Sub
-------------------------------------------
'La Qry SQL INSERT
-------------------------------------------

                strSQL = " INSERT INTO RPT_NMDT " _
                       & "(RptName, DatTrait, cle, NomPre, DN, NMFichier ) VALUES " _
                       & "(" & "'" & strRptNM & "'" & ", " & varDate & ", " _
                       & "'" & varCle & "'" & ", " & "'" & strNom & "'" & ", " & "'" & strDN & "'" & ", " _
                       & "'" & NomFichierListe & "'" & ")"
                       
                ' l'execution de la Qry
                MyDB.Execute strSQL, dbSQLPassThrough
-------------------------------------------
'END La Qry SQL INSERT
-------------------------------------------

-------------------------------------------
'La Qry SQL UPDATE
-------------------------------------------

          strSQL = " UPDATE RPT_NMDT " _
                 & " SET DatTrait = " & varDate & ", TempsTrait = '" & VarTemp & "'," _
                 & " NbrTrait = " & VarNbTrait & " , NMFichier = '" & NomFichierListe & "' " _
                 & " WHERE RptName = '" & strRptNM & "'" _
                 & " AND NMFichier = '" & NomFichierListe & "' "
                 
          MyDB.Execute strSQL, dbSQLPassThrough
-------------------------------------------
'END Qry SQL UPDATE
-------------------------------------------

-------------------------------------------
'Qry SQL DELET
-------------------------------------------

    strDate = "to_date(" & "'" & strDate & "'" & ", 'yyyy-mm-dd')"

    strSXL = " DELETE FROM RPT_NMDT " _
                & " WHERE RptName = '" & strRptNM & "'" _
                & " AND DatTrait = " & strDate & " "
    
    MyDB.Execute strSXL, dbSQLPassThrough

-------------------------------------------
' END Qry SQL DELET
-------------------------------------------

-------------------------------------------
' Qry ORACLE SQL PACKAG
-------------------------------------------

               strSQL = "Begin " + "pck_log.prc_add_log('" + _
                      process_id + "', " + _
                      CStr(severity) + ", '" + _
                      chaine + "', to_date('" + _
                      snapshoton + "','YYYY/MM/DD'));" + _
                       " end;"
               
                MyDB.Execute strSQL, dbSQLPassThrough

-------------------------------------------
' END Qry ORACLE SQL PACKAG
-------------------------------------------