Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    Question Unanswered: Access Update SQLstring in VBA HELP

    Hi all,
    This is my first post on the forum, i'm hoping you can help. I have a database table in Access 2010. I want to update a field in the database based on the values in a userform within Excel 2010. However, I am getting the following error message 'Syntax error in Update statement'. Not too helpful for me

    I have hovered my mouse over each Txt, Cmb value and the values are what I would expect. The Txt and Cmb field names are correct, as are the database field names. I believe all that are dates are dates in the access tables and the remainder are text, with the exception of Total Docs which is number and ID which is an AutoNumber. What am I missing?

    I believe the structure is correct, and if I substitute the form Txt and Cmb variables then run in Access SQL viewer it works?

    Thanks in advance for your help.

    Dim uvDBPath As String
    uvDBPath = "I:\Inputs\Secure\Department\COPYDOCS\Scanning Logs\MCT\Tag\DB - DO NOT ACCESS\DB Invoice Scanning.accdb;"

    Dim uvCmd As ADODB.Command
    Dim uvConnection As String

    uvConnection = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & uvDBPath

    sqlString = _
    "UPDATE Tbl_Invoice_Scanning SET" & _
    "[Machine No] = " & Chr(34) & Cmb_MachineNo.Value & Chr(34) & "," & _
    "[Batch ID] = " & Chr(34) & Txt_BatchID.Value & Chr(34) & "," & _
    "[Barcode First] = " & Chr(34) & Txt_Barcode.Value & Chr(34) & "," & _
    "[Barcode Last] = " & Chr(34) & Txt_BarcodeLast.Value & Chr(34) & "," & _
    "[Entity] = " & Chr(34) & Cmb_Entity.Value & Chr(34) & "," & _
    "[Mail Type] = " & Chr(34) & Cmb_MailType.Value & Chr(34) & "," & _
    "[Receipt_DT] = #" & Txt_ReceiptDate.Value & "#," & _
    "[Total Docs] = " & Txt_NoDocs.Value & "," & _
    "[Scan_DT] = #" & Txt_ScanDate.Value & "#," & _
    "[Scan User] = " & Chr(34) & Txt_ScanUser.Value & Chr(34) & "," & _
    "[QC_DT] = #" & Txt_QCDate.Value & "#," & _
    "[QC User] = " & Chr(34) & Txt_QCUser.Value & Chr(34) & "," & _
    "[Release_DT] = #" & Txt_ReleaseDate.Value & "#," & _
    "[Deletion_DT] = #" & Txt_DeletionDate.Value & "#," & _
    "[Deletion User] = " & Chr(34) & Txt_DeletionUser.Value & Chr(34) & ", " & _
    "WHERE (([ID])=" & Sheets("Data").Range("B" & uvRow).Value & ");"


    Set uvCmd = New ADODB.Command
    With uvCmd
    .CommandText = sqlString
    .ActiveConnection = uvConnection
    .Execute
    End With

    Set uvCmd = Nothing

  2. #2
    Join Date
    Aug 2012
    Posts
    7
    Well, I didn't need your help after all. That's not to say I didn't need help. The problem was that I had a comma after my last row before the WHERE clause. Removing this and it worked. Only 3 hours wasted over a comma.

    Another question. How can I manage blank date fields within the string. As it falls over if there isn't a date present. Can I add an IF statement within the string? I don't want to have the string multiple times if I can avoid it.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either set a valid date as a default value or use NULL
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Additionally, you can conditionally build the string. Generally:

    Code:
    sqlString = "Build the string up until that point
    
    If IsDate(YourDateField) Then
      sqlString = sqlString & "Add the bit for that field"
    End If
    
    sqlString = sqlString & "Finish the string here"
    You may find this useful for debugging:

    Debugging
    Paul

  5. #5
    Join Date
    Aug 2012
    Posts
    7
    Just to keep you all uptodate, I have solved it by adding some validation when they click submit to DB.

    If Txt_DeletionDate.Value = "" Then
    uvDeletionDate = "[Deletion_DT] = Null, "
    Else: uvDeletionDate = "[Deletion_DT] = #" & Txt_DeletionDate.Value & "#, "
    End If

    The final SQL string then encorprates the variables as follows. works a treat.

    sqlstring = _
    "UPDATE Tbl_Invoice_Scanning SET " & _
    "[Machine No] = " & Chr(34) & Cmb_MachineNo.Value & Chr(34) & "," & _
    "[Batch ID] = " & Chr(34) & Txt_BatchID.Value & Chr(34) & "," & _
    "[Barcode First] = " & Chr(34) & Txt_Barcode.Value & Chr(34) & "," & _
    "[Barcode Last] = " & Chr(34) & Txt_BarcodeLast.Value & Chr(34) & "," & _
    "[Entity] = " & Chr(34) & Cmb_Entity.Value & Chr(34) & "," & _
    "[Mail Type] = " & Chr(34) & Cmb_MailType.Value & Chr(34) & "," & _
    "[Receipt_DT] = #" & Txt_ReceiptDate.Value & "#," & _
    "[Total Docs] = " & Txt_NoDocs.Value & "," & _
    "[Scan_DT] = #" & Txt_ScanDate.Value & "#," & _
    "[Scan User] = " & Chr(34) & Txt_ScanUser.Value & Chr(34) & "," & _
    uvQCDate & _
    "[QC User] = " & Chr(34) & Txt_QCUser.Value & Chr(34) & "," & _
    uvReleaseDate & _
    uvDeletionDate & _
    "[Deletion User] = " & Chr(34) & Txt_DeletionUser.Value & Chr(34) & " " & _
    "WHERE (([ID])=" & Sheets("Data").Range("B" & uvRow).Value & ");"

Tags for this Thread

Posting Permissions

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