Hi;

I want to update a MSAccess record with data from excel and the system is giving " Runtime Error -2147217900(8004e14)' Synatx Error in update statement

this is the code :

Sub UpdateDataAccess()
Dim PathMyApplication As Variant
Dim TargetRange As Variant
Dim Nbrecords As Integer

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim intColIndex As Integer



Dim m As Integer
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Dim Var5 As String
Dim Var6 As String
Dim Var7 As String
Dim Var8 As String
Dim Var9 As String
Dim Var10 As String
Dim Var11 As String
Dim Var12 As String
Dim Var13 As String
Dim Var14 As String
Dim Var15 As String
Dim Var16 As String
Dim Var17 As String
Dim Var18 As String
Dim Var19 As String
Dim Var20 As String
Dim Var21 As String
Dim Var22 As String

Dim PINNUM As String
Range("A1").Select
ChDir _
"C:\Documents and Settings\" & GetUserName & "\My Documents\WM_blarkin1 My Documents\Personal"
Workbooks.Open Filename:= _
"C:\Documents and Settings\" & GetUserName & "\My Documents\WM_blarkin1 My Documents\Personal\INPROCESS.xls"
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & "C:\Fleetcom\fleetcom.mdb;"
Set rsData = New ADODB.Recordset
'Inscrit les valeurs de la feuille Excel dans la table ListeClients
For i = 2 To Sheets("Inprocess").Range("A65536").End(-4162).Row
Nbrecords = Sheets("inprocess").Range("A65536").End(-4162).Row - 1
' Application.StatusBar = "Ajout de l'enregistrement " & I - 1 & " sur " & Nbrecords & " dans la table Clients ..."
' If Sheets("HFPU").Range("X" & i).Value = "Y" Then
PINNUM = Sheets("inprocess").Cells(i, 2).Value
Var1 = Sheets("Inprocess").Cells(i, 1).Value
Var2 = Sheets("Inprocess").Cells(i, 2).Value
Var3 = Sheets("Inprocess").Cells(i, 3).Value
Var4 = Sheets("Inprocess").Cells(i, 4).Value
Var5 = Sheets("Inprocess").Cells(i, 5).Value
Var6 = Sheets("Inprocess").Cells(i, 6).Value
Var7 = Sheets("Inprocess").Cells(i, 7).Value
Var8 = Sheets("Inprocess").Cells(i, 8).Value
Var9 = Sheets("Inprocess").Cells(i, 9).Value
Var10 = Sheets("Inprocess").Cells(i, 10).Value
Var11 = Sheets("Inprocess").Cells(i, 11).Value
Var12 = Sheets("Inprocess").Cells(i, 12).Value
Var13 = Sheets("Inprocess").Cells(i, 13).Value
Var14 = Sheets("Inprocess").Cells(i, 14).Value
Var15 = Sheets("Inprocess").Cells(i, 15).Value
Var16 = Sheets("Inprocess").Cells(i, 16).Value
Var17 = Sheets("Inprocess").Cells(i, 17).Value
Var18 = Sheets("Inprocess").Cells(i, 18).Value
Var19 = Sheets("Inprocess").Cells(i, 19).Value
Var20 = Sheets("Inprocess").Cells(i, 20).Value
Var21 = Sheets("Inprocess").Cells(i, 21).Value
Var22 = Sheets("Inprocess").Cells(i, 22).Value

Application.ScreenUpdating = False


' Crée la chaine de connexion
' szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & "P:\Fleetcom\fleetcom.mdb;"
' Crée l'instruction SQL

szSQL = "UPDATE Inprocess SET Unit=" & Var2 & ",SJCode=" & Var4 & ",DueDate=" & Var5 & ",Overdue=" & Var6 & ",Make=" & Var7 & ",Model=" & Var8 & ",Year=" & Var9 & ",Desc=" & Var10 & ",Location=" & Var11 & ",Confirmed=" & Var12 & ",Date_Conf=" & Var13 & ",Received=" & Var14 & ",Date_Rec=" & Var15 & ",RO=" & Var16 & ",WIP=" & Var17 & ",WIP_Date=" & Var18 & ",Time_Rem=" & Var19 & " WHERE unit Like '" & PINNUM & "'"
'& " WHERE Inprocess.Unit Like " & PINNUM WHERE unit = ' " & PINNUM & " ' "
'",Delay=" & Var20 & ",Ready=" & Var21 & ",VMS=" & Var22 &
'ID= " & Var1 &
' szSQL = "SELECT Terminal,Route,Date,Company,Address,Receiver,PIN FROM DEPOT " & "WHERE (((Depot.company) Like '" & MyCritere & "'));"
' szSQL = "SELECT terminal,Company FROM POD" & " WHERE POD.teminal Like '" & mycritere & "'"




' Crée l'objet Recordset et exécute la requête
MsgBox szSQL, vbInformation, "SQL statement is....."

' rsData.Open szSQL, szConnect, adLockReadOnly, adLockReadOnly, adCmdText
rsData.Open szSQL, szConnect, adLockOptimistic, , adCmdText
' Application.StatusBar = "Lancement de la requête SQL|SQL Query Starts ..."
' On s'assure qu'il y a des enregistrements à récupérer ...
Next i

End Sub


Could you please review the code and correct it

thanks

Khelil

Okay....


Any of the fields text datatypes? If so, you need to wrap the text in single quotes

and if you are sending dates...

Hi

Not withstanding previouse posts, I think you need to declare, instantiate and Open a connection OBJECT (szConnect seem to be a connection string for the connection object!), unless I have missed it??

Then I suggest you use the Execute method on the connection object with an UPDATE query (as this dosn't return any records)?

This could be all rubbish but ...?


MTB