Discussion :

[mlww]

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

[Invité]

Bonjour,

S = S & "Hir_DatClo='" & Me.TextBox16.Value & "',"

[mlww]

Merci dysorthographie

je n'ai pas vus cette virgule,
mais le problème a continué
j'avais un espace a la fin du du nom du champ
mais dans la base de donné
ça pas était évident al le trouver

merci de ton aide