none
Syntax Question - CurrentDB.Execute/DoCmd.RunSQL Access 2010

    Question

  • Hi,

    I apologize if this looks familiar to some of you.  My hats off to you guys for helping us out on this forum.

    I don't really get some of the concepts used to escape values in VBA when running a sql command.  I've read that I need to encapsulate? text values with either single quotes or double quotes.  Dates need to be encapsulated in #'s, and numbers need to be encapsulated in ()?  Not sure on that last one..   

    I've also read that CurrentDB.execute and DoCmd.RunSQL have a different syntax used for when I reference values from form controls.  I also don't know when I should be using Me.boxA or [Forms]![frmBatchUpdate]![boxA] to reference data in an input box. 

    DoCmd.RunSQL (UPDATE tblTest _
    SET FieldZ = Me.boxZ.value _
    WHERE ( FieldA Like "*" & Me.boxA.value & "*" ) _
    AND ( FieldB Like "*" & Me.boxB.value & "*" ) _
    AND ( FieldC Like "*" & Me.boxC.value & "*" ) _
    AND ( FieldD Like "*" & Me.boxD.value & "*" ) _
    AND ( FieldE Like "*" & Me.boxE.value & "*" ) _
    AND ( FieldF Like "*" & Me.boxF.value & "*" ) _
    AND ( FieldG Like "*" & Me.boxG.value & "*" ))


    UPDATE ResultsTable _
    SET FieldZ = [Forms]![frmBatchUpdate]![boxZ] _
    WHERE ( FieldA Like "*" & [Forms]![frmBatchUpdate]![boxA] & "*" ) _
    AND ( FieldB Like "*" & [Forms]![frmBatchUpdate]![boxB] & "*" ) _
    AND ( FieldC Like "*" & [Forms]![frmBatchUpdate]![boxC] & "*" ) _
    AND ( FieldD Like "*" & [Forms]![frmBatchUpdate]![boxD] & "*" ) _
    AND ( FieldE Like "*" & [Forms]![frmBatchUpdate]![boxE] & "*" ) _
    AND ( FieldF Like "*" & [Forms]![frmBatchUpdate]![boxF] & "*" ) _
    AND ( FieldG Like "*" & [Forms]![frmBatchUpdate]![boxG] & "*" );

    Can anyone shed some light here?  I believe the above sql update statement is correct in concept, but will fail when I put it next to the DoCMD.RunSQL or Currentdb.execute statements within VBA.

    Thanks for your time and suggestions!

    Tuesday, July 09, 2013 7:07 PM

Answers

  • You need the output String in the Immediate/Debug window to look like:

    UPDATE TestTable  
    SET FieldA = "test1", 
        FieldB = "test2", 
        FieldC = "Test4", 
        FieldD = "Test5" 
    WHERE ((FieldE LIKE "*bananas*") 
       AND (FieldF LIKE "*Trees*") 
       AND ((FieldG LIKE "*Toys*") 
       AND (FieldH LIKE "*Win*") 
       AND (FieldI LIKE "*Turkeys*") )

    (line-breaks added for clarity only)

    This is the String that will be executed by JET/ACE.

      


    Van Dinh

    • Marked as answer by Mikevaprofile Thursday, July 11, 2013 8:53 PM
    Thursday, July 11, 2013 8:41 PM

All replies

  • Neither is correct if you want to construct the SQL String dynamically by VBA code.  Remember that VBA needs to construct the String from your statement(s) before passing the constructed SQL String to Access Database Engine (ACE) for execution using either RunSQL or Execute.

    In my last post in the earlier thread Access 2010 - SQL Update Syntax, I asked you to test with the saved Query [FieldZUpdateQuery] to make sure the saved Query works as expected.  If this works, the SQL String of the saved Query would be the constructed SQL String you want for the RunSQL Method.  In your VBA, the VBA statement will need more than the SQL statement obviously since you need to instruct VBA to perform concatenations.

    With RunSQL, Access VBA can resolve the Control references to required values automatically for you.  With Execute Method (which is the ACEDAO's Method, not Access'), the resolution is not done automatically.  Thus, if you want to use the Execute Method, you need to resolve the Control references to values by your own VBA code in the SQL String construction also.

    I would recommend that you test your saved Query [FieldZUpdateQuery] using the GUI first to make sure it works.  Once it works, you know the SQL String you need to use with the RunSQL Method in VBA.  You can then try to construct the SQL String (for RunSQL Method) dynamically by VBA code using the concatenation operator ampersand (&) and in the code, you can check the constructed SQL String with the Debug statement like:

    Dim strSQL As String
      
    strSQL = "UPDATE ...    " & _
      " ... " & _
      " ... " & _
      " ... "
      
    Debug.Print strSQL  ' Check the Immediate/Debug window for output
      
    DoCmd.RunSQL strSQL

    Now, if the String outputted in the Immediate/Debug window is exactly the same as the SQL String in your saved Query, the RunSQL Method should work fine.

    I leave the SQL String construction for the Execute Method later until you get the SQL String construction for the RunSQL Method correctly first to avoid confusions.

    BTW, in my second last post in the earlier thread, I did ask you to post the VBA code that constructs the SQL String (since I anticipated that you would have problems constructing the SQL String in code) and you did not post your VBA code for SQL String construction (you switched to saved Query and post the SQL String of the saved Query + code to Execute the saved Query instead).

     


    Van Dinh

    Wednesday, July 10, 2013 12:41 AM
  • to update a table using DoCmd.RunSql -- you need to do something like this:

    DoCmd.RunSql "Update table1 Set fld1 = 'xyz', fld2 = 'abc', fld3 = 123, fld4 = #2/22/2022#"

    or

    DoCmd.RunSql "Update table1 Set fld1 = 'xyz', fld2 = 'abc', fld3 = 123, fld4 = #2/22/2022# Where somefld = 'somevalue'"

    --in this sample I say the fld1 is a text field, fld2 is a text field, field3 is a number field, and fld4 is a date/Time field.

    -- in Access Jet sql you delimit text values with --'-- single quotes and date values with the # pound symbol, and numeric values do not get delimited


    Rich P

    Wednesday, July 10, 2013 3:18 PM
  • Well I think I'm coming closer.  Much thanks for the debug.print and other suggestions. 

    I'm dynamically constructing the sql query string like the second method you mentioned above.  The sql query string output looks exactly like this.  I grabbed this straight from the debug.print output. 

    UPDATE TestTable  SET FieldA = test1, FieldB = test2, FieldC = Test4, FieldD = Test5 WHERE ((FieldE LIKE *bananas*) AND (FieldF LIKE *Trees*) AND ((FieldG LIKE *Toys*) AND (FieldH LIKE *Win*) AND (FieldI LIKE *Turkeys*) )

    and I'm getting an 3075 missing operator error.  I've tried adding the table reference like (table.field) and it made no difference to the the error message I keep getting.

    Here's the code after I've dynamically built it. 

    vSQL = vUpdate & vSet & vWhere '& ";" I've commented out the last part as it has no effect.

    'msgbox to assist with troubleshooting error....
    MsgBox vSQL
    Debug.Print vSQL

    DoCmd.RunSQL vSQL

    Thursday, July 11, 2013 8:07 PM
  • You need the output String in the Immediate/Debug window to look like:

    UPDATE TestTable  
    SET FieldA = "test1", 
        FieldB = "test2", 
        FieldC = "Test4", 
        FieldD = "Test5" 
    WHERE ((FieldE LIKE "*bananas*") 
       AND (FieldF LIKE "*Trees*") 
       AND ((FieldG LIKE "*Toys*") 
       AND (FieldH LIKE "*Win*") 
       AND (FieldI LIKE "*Turkeys*") )

    (line-breaks added for clarity only)

    This is the String that will be executed by JET/ACE.

      


    Van Dinh

    • Marked as answer by Mikevaprofile Thursday, July 11, 2013 8:53 PM
    Thursday, July 11, 2013 8:41 PM
  • UPDATE TestTable  SET FieldA = test1, FieldB = test2, FieldC = Test4, FieldD = Test5 WHERE ((FieldE LIKE *bananas*) AND (FieldF LIKE *Trees*) AND ((FieldG LIKE *Toys*) AND (FieldH LIKE *Win*) AND (FieldI LIKE *Turkeys*) )

    You need to delimit your args with single quotes for text values, # symbol for date values (Access Jet sql only), number values are not delimited.

    FieldE LIKE *bananas*

    should be

    FieldE LIKE '*bananas*'

    and so on

    And one other thing I'm noticing is that your sample has a lot of text fields you are comparing against.  This leads me to think that your table(s) is/are not normalized.  That would be a significant factor contributing to your problem.


    Rich P

    Thursday, July 11, 2013 8:47 PM
  • It's now working, and I am dancing around my computer!  You guys rock!

    THanks so much for your help!

    Thursday, July 11, 2013 8:54 PM