Results 1 to 4 of 4

Thread: Syntax Error In SQL Update Query Statement

  1. #1
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0

    Syntax Error In SQL Update Query Statement

    Hi All,

    i am trying to run a update code from vba through adodb connection my Db is .MDB,
    while runnign the update query i am getting "syntax error in update statement" error please help
    As i am where i did it worng
    my code so far
    Code:
    Private Sub save_btn_Click()
    endtime = Format(Now(), "HH:MM:SS")
    dat = Format(Now(), "DD/MM/YYYY")
    num = Me.unique_text.Text
    str1 = "update [Tab] set Territory ='" & Me.com_ter.Value & "',Tower = '" & Me.com_tow.Value & "',Activity ='" & Me.com_activity.Value & "',Sub_Activity ='" & Me.com_subactivity.Value & "',Processor_Name = '" & Me.com_processor.Value & "',Start_Time ='" & starttime & "',Received_Date=#" & Me.received_text.Text & "#,Due_Date =#" & Me.due_text.Text & "#,Processed_Date=#" & Me.processed_text.Text & "#,Subject='" & Me.subject_text.Text & "',Comments='" & Me.comments_text.Text & "',Action='" & Me.com_status.Value & "',Reason ='" & Me.reason_text.Text & "',End_Time='" & endtime & "',Status_Date=#" & dat & "# where tab.Id = " & num
    Me.open_connection_product
    
    con.Execute str1
    
    Module1.close_connection
    
    End Sub
    
    Public Sub open_connection_product()
    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    
    path = Sheets("PRG").Range("A65536").Value
    constring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Jet OLEDB:Database Password=pass"
    
    If con.State = 0 Then
    con.Open constring
    End If
    End Sub

  2. #2
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0
    for those searching the same answer;
    i got it working the reason error occured was due to no space given after each "="

    cheers

  3. #3
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13
    Quote Originally Posted by ashu1990 View Post
    for those searching the same answer;
    i got it working the reason error occured was due to no space given after each "="

    cheers
    Hi Ashu,
    It was not beacause of no spaces after '='. You were getting an error because there is a field named 'Action' in your table,that is a keyword.Put that in square brackets. e.g. [Action]
    Last edited by Transformer; 06-08-2013 at 09:58 AM.
    Regards,

    Transformer

  4. #4
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0
    Hey thanks transformer..
    i did the both adding space and the brackets together so forgot to mentioned that thanks for the reply..

Similar Threads

  1. Access Query Help
    By Vipergs8v10 in forum Access Help
    Replies: 2
    Last Post: 05-08-2013, 06:32 PM
  2. VBA Procedure for nested if statement.
    By _google in forum Excel Help
    Replies: 1
    Last Post: 02-08-2013, 10:16 PM
  3. Dynamic Chart Query
    By leopaulc in forum Excel Help
    Replies: 6
    Last Post: 11-26-2012, 04:50 PM
  4. Replies: 2
    Last Post: 11-17-2011, 07:49 PM
  5. On Error Statement (Visual Basic)
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 10-12-2011, 09:06 AM

Posting Permissions

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