Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

VBA SQL UPDATE issue

Discussion in 'Information Technology' started by DJ, Nov 16, 2005.

  1. DJ

    DJ
    Expand Collapse
    Guest

    I have an UPDATE SQL statement in VBA which is giving me an error, running
    in code, but runs when I copy and paste the SQL into the query window. I am
    getting a -2147217900 error, Syntax error in UPDATE statement, from the JET
    engine. When I copy the SQL string and place it into a query, I get no
    error and the update is made.

    Below is the SQL string. All of the field names are correct.

    "UPDATE tblGroup SET Active=True, SessionType='Full Day', Status=1,
    StatusAsOfDate=#11/14/2005#, StatusComments='', STARLEVEL=3,
    STARLEVELAsOfDate=#11/14/2005#, STARLEVELSource='Contracts', NewSpaces=NULL,
    Language='', UpdUserID=1, UpdDate=#11/15/2005#, UpdTime=#4:49:50 PM# WHERE
    AgencyID='0104' AND CenterID='012' AND ClassroomID='12' AND SessionID='0'
    AND GroupID='0'"

    Can anyone help me with this? Thank you in advance.
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    Try the following

    strSql = "your sql"

    debug.print strSql <--- add this line RIGHT before you execute

    docmd.RunSql strSql

    After your code fails, do a ctrl-g to get to the debug window, now, cut and
    paste the sql into the query builder...

    does it run un-modifed?


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. DJ

    DJ
    Expand Collapse
    Guest

    I have figured it out. Apparently the Language field was causing a problem
    only in VBA. I have changed the field name and it is working. Thanks to
    anyone who put any time into this.


    "DJ" <nospam@hotmail.com> wrote in message
    news:%23ua1euk6FHA.1276@TK2MSFTNGP09.phx.gbl...
    >I have an UPDATE SQL statement in VBA which is giving me an error, running
    > in code, but runs when I copy and paste the SQL into the query window. I
    > am
    > getting a -2147217900 error, Syntax error in UPDATE statement, from the
    > JET
    > engine. When I copy the SQL string and place it into a query, I get no
    > error and the update is made.
    >
    > Below is the SQL string. All of the field names are correct.
    >
    > "UPDATE tblGroup SET Active=True, SessionType='Full Day', Status=1,
    > StatusAsOfDate=#11/14/2005#, StatusComments='', STARLEVEL=3,
    > STARLEVELAsOfDate=#11/14/2005#, STARLEVELSource='Contracts',
    > NewSpaces=NULL,
    > Language='', UpdUserID=1, UpdDate=#11/15/2005#, UpdTime=#4:49:50 PM# WHERE
    > AgencyID='0104' AND CenterID='012' AND ClassroomID='12' AND SessionID='0'
    > AND GroupID='0'"
    >
    > Can anyone help me with this? Thank you in advance.
    >
    >
    >
     
  5. tina

    tina
    Expand Collapse
    Guest

    if this is your SQL statement *exactly* as it's saved in the VBA module,
    then you need to concatenate separate lines, rather than just enclosing the
    whole in double quotes, as

    "UPDATE tblGroup SET Active=True, SessionType='Full Day', " _
    & "Status=1, StatusAsOfDate=#11/14/2005#, " _
    & "StatusComments='', STARLEVEL=3, " _
    & "STARLEVELAsOfDate=#11/14/2005#, " _
    & "STARLEVELSource='Contracts', NewSpaces=NULL, " _
    & "Language='', UpdUserID=1, UpdDate=#11/15/2005#, " _
    & "UpdTime=#4:49:50 PM# WHERE AgencyID='0104' " _
    & "AND CenterID='012' AND ClassroomID='12' AND " _
    & "SessionID='0' AND GroupID='0'"

    if you already have the SQL statement correct concatenated, then copy the
    SQL statement directly from the VBA code module so we can look at it.

    hth


    "DJ" <nospam@hotmail.com> wrote in message
    news:%23ua1euk6FHA.1276@TK2MSFTNGP09.phx.gbl...
    > I have an UPDATE SQL statement in VBA which is giving me an error, running
    > in code, but runs when I copy and paste the SQL into the query window. I

    am
    > getting a -2147217900 error, Syntax error in UPDATE statement, from the

    JET
    > engine. When I copy the SQL string and place it into a query, I get no
    > error and the update is made.
    >
    > Below is the SQL string. All of the field names are correct.
    >
    > "UPDATE tblGroup SET Active=True, SessionType='Full Day', Status=1,
    > StatusAsOfDate=#11/14/2005#, StatusComments='', STARLEVEL=3,
    > STARLEVELAsOfDate=#11/14/2005#, STARLEVELSource='Contracts',

    NewSpaces=NULL,
    > Language='', UpdUserID=1, UpdDate=#11/15/2005#, UpdTime=#4:49:50 PM# WHERE
    > AgencyID='0104' AND CenterID='012' AND ClassroomID='12' AND SessionID='0'
    > AND GroupID='0'"
    >
    > Can anyone help me with this? Thank you in advance.
    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page