Bojour,,
j'ai tourné en rond tout la journée sans rien trouvé
je désires faire une mise a jour d'un enregistrement dans un table access depuis excel

J'ai comme message erreur de syntaxe, j'ai essayé plusieur combinaisons, mais sans succes

voici comment je construit ma requette sql
Sachant que e champs Hir_ID est du texte dans ma bdd

merci de me dire si vous voyez quelque chose
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
    S = "Hir_ID='" & Me.TextBox0.Value & "',"
    S = S & "Tic_Ticket='" & Me.TextBox1.Value & "',"
    S = S & "Tic_Datum='" & Me.TextBox2.Value & "',"
    S = S & "Tic_Zeit='" & Me.TextBox3.Value & "',"
    S = S & "Tic_Anlage_ID='" & Replace(Me.TextBox4.Value, "'", "''") & "',"
 
            TransTxT = Replace(Me.TextBox5.Value, "'", "''"): TransTxT = Replace(TransTxT, vbCrLf, "|")
    S = S & "Tic_Exakter_Name='" & TransTxT & "',"
 
    S = S & "Tic_Objekt_ID='" & Replace(Me.TextBox6.Value, "'", "''") & "',"
    S = S & "Tic_Raumnummer='" & Replace(Me.TextBox7.Value, "'", "''") & "',"
 
            TransTxT = Replace(Me.TextBox8.Value, "'", "''"): TransTxT = Replace(TransTxT, vbCrLf, "|")
    S = S & "Tic_Meldung='" & TransTxT & "',"
 
    S = S & "Tic_Name_Vorname='" & Replace(Me.TextBox9.Value, "'", "''") & "',"
 
            TransTxT = Replace(Me.TextBox10.Value, "'", "''"): TransTxT = Replace(TransTxT, vbCrLf, "|")
    S = S & "Tic_Ruckmeldung='" & TransTxT & "',"
 
    S = S & "Tic_Abteilung='" & Me.TextBox11.Value & "',"   'Variable ListIndex [S = S & "Tic_Abteilung='" & Me.CBoxSer.ListIndex & "',"]
    S = S & "Tic_Telefon='" & Me.TextBox12.Value & "',"
    S = S & "Tic_E_Mail='" & Me.TextBox13.Value & "',"
    S = S & "Tic_Status='" & Me.TextBox14.Value & "',"
    S = S & "Hir_DatInt='" & Me.TextBox15.Value & "',"
    S = S & "Hir_DatClo='" & Me.TextBox16.Value & "',"
    Update_DB "Data", S, "Hir_ID='" & Me.TextBox0.Value & "'"
End If
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
 
Sub Update_DB(Tbl As String, UPD As String, Cond As String)
    Req = "UPDATE [" & Tbl & "] SET " & UPD & " WHERE " & Cond
    Debug.Print Req
    Lig = Query(Req)
End Sub
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
Function Query(Req As String, Optional Head As Byte = 1) As Long
Dim Cnx As Object, Rst As Object
Dim T As Variant, Col_SQL As Integer, i As Long, j As Long
BDD = ActiveWorkbook.Path & "\Bdd_Gmao.accdb"
 
    On Error GoTo errhdlr
    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Provider = "MSDASQL"
 
    Cnx.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & BDD
 
    Cnx.Execute Req
    Query = 0
 
    Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
    Exit Function
 
errhdlr:
    If Not Rst Is Nothing Then If Rst.State = 1 Then Rst.Close
    If Not Cnx Is Nothing Then If Cnx.State = 1 Then Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
    Query = -1
    MsgBox (Err.Description)
End Function