Mettre a jour via ODBC

[sampla]

Bonjour, je viens de découvrir odbc
ca marche bien, lorsque je modifie les données sur ma base actualise je vois apparaitre les nouvelle donnée sur excel.

Mais j'arrive pas à faire le contraire : modifier sur excel et que ca s'actualise ma base.

Comment je peux faire ?

 

[Compte Supprimé 979]

Re : Mettre a jour via ODBC

Bonjour Sampla,

Après une petite recherche sur le net 🙄

J'ai trouvé ça ODBC insert process

Sub insertdata() 'soruceDBkey, DAILY_DATE, CODE, VALUE)     'test cases
     
    Dim ws As Worksheet 
    Dim DBkey As String 
    Dim DAILYDATE() As String 
    Dim CODE() As String 
    Dim VALUE() As String 
     
    Dim sqlStmt As String 
     'Dim sqlstmtdelete As String
    Dim connectionStr As String 
    Dim wrkODBC As Workspace 
    Dim cnWERP As Connection 
    Dim rsLIMS As Recordset 
     
    Set ws = Sheets(G211_C) 
    PrefDATEr = Range("F65000").End(xlUp).Row 
    soruceDATEr = Range("b65000").End(xlUp).Row 
     
    If soruceDATEr > PrefDATEr Then 
        DBkey = ws.Range("f6") 
        DAILYDATE = ws.Range("B" & PrefDATEr & ":B" & soruceDATEr) 
        CODE = ws.Range("C" & PrefDATEr & ":C" & soruceDATEr) 
        VALUE = ws.Range("D" & PrefDATEr & ":D" & soruceDATEr) 
         
    End If 
     
     'Setup WERP Database
    Set wrkODBC = CreateWorkspace("WERPworkspace", _ 
    "admin", "", dbUseODBC) 
     'Set wrkODBC = CreateWorkspace("WERPworkspace", _
     '     "admin", "", dbUseODBC)
     'open connectionobject supplied informatio in the connect string
    connectionStr = "ODBC;DATABASE=WRED;UID=pub;PWD=pub;DSN=WRED" 
     
    Set cnWRED = wrkODBC.OpenConnection("DBHYDRO", _ 
    dbDriverNoPrompt, , _ 
    connectionStr) 
     
    wrkODBC.BeginTrans 
     
     'need testing added here
     
    sqlStmt = "insert into DM_DAILY_DATA(DBKEY, DAILY_DATE, CODE, VALUE) values ('" & DBkey & "','" & DAILYDATE & "','" & CODE & "','" & VALUE & "')" 
     
    cnWERP.Execute (sqlStmt) 
    wrkODBC.commitTrans 
    cnWRED.Close 
    End 
     
    ErrHandler 
    wrok.rollback 
    cnWRED.Close 
    response = MsgBox(errMsg, vbCritical, "Error") 
End Sub 
 
Function Validate_Field(cnWRED As Connection, sqlStmt As String) As Boolean 
    Dim rs As Recordset 
    Dim status As Boolean 
    Set rs = cnWRED.openRecordset(sqlStmt) 
     
    If rs.fields(0) > 0 Then 
        status True 
    Else 
        status = False 
    End If 
     
    Validate_Fiels = status 
     
End Function