Results 1 to 4 of 4

Thread: Excel and ADO

  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Question Unanswered: Excel and ADO

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Okay....


    Any of the fields text datatypes? If so, you need to wrap the text in single quotes
    Code:
    "UPDATE mytable SET some_text_field = '" & @var & "'"
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    and if you are sending dates...

    Code:
    UPDATE MY_TABLE SET SOME_DATE_FIELD=#" & @VAR & "#"
    Inspiration Through Fermentation

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •