Welcome to SPN

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

Sign Up Now!

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.
    >
    >
    >
     

Share This Page