Results 1 to 12 of 12
  1. #1
    Join Date
    May 2011
    Posts
    19

    Exclamation Unanswered: VBA problem - code not working

    Dim StrSql As String
    Dim StrSql2 As String

    StrSql = "SELECT [LANARK_INTF_HSMS $ CLI_EVENT].* INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    '" WHERE ((([LANARK_INTF_HSMS $ CLI_EVENT].ROCC_EVENT_STATUS)='W' Or ([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R'));"

    StrSql2 = "UPDATE [LANARK_INTF_HSMS$CLI_EVENT] SET [LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS = Null " _
    '& "WHERE (([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='W') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4)) OR ((([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4));"


    'DoCmd.SetWarnings False
    'DoCmd****nSQL (StrSq)
    'DoCmd****nSQL StrSql2
    'DoCmd.SetWarnings True

    ***************************************

    any help ... the system gives an error > runtime error 3450

  2. #2
    Join Date
    May 2011
    Posts
    19

    Question VBA problem - code not working

    Dim StrSql As String
    Dim StrSql2 As String

    StrSql = "SELECT [LANARK_INTF_HSMS $ CLI_EVENT].* INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    '" WHERE ((([LANARK_INTF_HSMS $ CLI_EVENT].ROCC_EVENT_STATUS)='W' Or ([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R'));"

    StrSql2 = "UPDATE [LANARK_INTF_HSMS$CLI_EVENT] SET [LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS = Null " _
    '& "WHERE (([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='W') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4)) OR ((([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4));"


    'DoCmd.SetWarnings False
    'DoCmd****nSQL (StrSq)
    'DoCmd****nSQL StrSql2
    'DoCmd.SetWarnings True

    ***************************************

    any help ... the system gives an error > runtime error 3450

  3. #3
    Join Date
    May 2011
    Posts
    19

    SQL in VBA problem , it could be rather simple .. yet i cant see it, help plz

    Dim StrSql As String
    Dim StrSql2 As String

    StrSql = "SELECT [LANARK_INTF_HSMS $ CLI_EVENT].* INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    '" WHERE ((([LANARK_INTF_HSMS $ CLI_EVENT].ROCC_EVENT_STATUS)='W' Or ([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R'));"

    StrSql2 = "UPDATE [LANARK_INTF_HSMS$CLI_EVENT] SET [LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS = Null " _
    '& "WHERE (([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='W') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4)) OR ((([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R') AND (([LANARK_INTF_HSMS$CLI_EVENT].EVENT_TYPE)=4));"


    'DoCmd.SetWarnings False
    'DoCmd****nSQL (StrSq)
    'DoCmd****nSQL StrSql2
    'DoCmd.SetWarnings True

    ***************************************

    any help ... the system gives an error > runtime error 3450

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    so what db back end are you using?
    what front end are you using
    what does your SQL actually look like rather than the code looks like
    what error message do you get?

    on the face of it that looks like VBA, and VBA in Access, are you using MySQL, JET or SQL Server or something else altogether?

    at first glance it looks like you have an extraneous ' (or ") adjacent to the & on the second line of strSQL2 but its hard to tell as you haven't identified what line is throwing the error.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first thing toi do is to tidy up your SQL, if its tricky to read then its tricky to find where the errorrs are
    so
    consider using table aliases where there is more than one table in a query
    eg
    [code] select my,comma,separated,at.column,list from atable as at
    join anothertable as bt on bt.column=at.column
    consider not specifying table.column names unless there is a specific reason

    eg
    Code:
    StrSql = "SELECT [LANARK_INTF_HSMS $ CLI_EVENT].* INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    '" WHERE ((([LANARK_INTF_HSMS $ CLI_EVENT].ROCC_EVENT_STATUS)='W' Or ([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R'));"
    becomes
    Code:
    StrSql = "SELECT * INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    '" WHERE ROCC_EVENT_STATUS='W' Or ROCC_EVENT_STATUS='R';"
    then consider using some of the other features of SQL such as the in subclause
    Code:
    StrSql = "SELECT * INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT] 
    WHERE ROCC_EVENT_STATUS=in ('W','R');"
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2011
    Posts
    19

    thank you

    i am using access 2003 , the error appears in the first half to start with .. i have tried many things but its rather confusing ..

    Quote Originally Posted by healdem View Post
    OK
    so what db back end are you using?
    what front end are you using
    what does your SQL actually look like rather than the code looks like
    what error message do you get?

    on the face of it that looks like VBA, and VBA in Access, are you using MySQL, JET or SQL Server or something else altogether?

    at first glance it looks like you have an extraneous ' (or ") adjacent to the & on the second line of strSQL2 but its hard to tell as you haven't identified what line is throwing the error.

  7. #7
    Join Date
    May 2011
    Posts
    19

    p.s.

    StrSql = "SELECT [LANARK_INTF_HSMS $ CLI_EVENT].* INTO " & MFile1 & " FROM [LANARK_INTF_HSMS $ CLI_EVENT]" & _
    " WHERE ((([LANARK_INTF_HSMS $ CLI_EVENT].ROCC_EVENT_STATUS)='W' Or ([LANARK_INTF_HSMS$CLI_EVENT].ROCC_EVENT_STATUS)='R'));"

  8. #8
    Join Date
    May 2011
    Posts
    19

    plese see attached error i am getting

    still no joy
    Attached Thumbnails Attached Thumbnails vba.png  

  9. #9
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try this:
    DoCmd****nSQL Strsql

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    please do not cross post the same question in mulitple forums, its confusing, it doens't help you get an answer to a (JET) SQL problem asking int he VB forum. although VB & VBA are related they are not he same.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads from MySQL & VB forums merged into the same question in the Access forum
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what that error message is telling you is
    that you hgave an error in your SQL statement
    so we need to see the actual SQL as opposed to the code that builds the SQL

    most likely reasons are :-
    a fault in your SQL syntax
    you are missing a ' or " used to encapsulate text / string / char literals
    missing or misstyped column / table / other name

    in this case my suspicion is going to be that there is no value or a corrupt value in MFile1

    as the code compiles its a fault in the way you've built your SQL

    the easiest way of proving that is the examine what the SQL actually is
    run the code
    when it halts
    move your mouse to the immediuate window and display the value of strSQL
    or put a msgbox strSQL inbetween where you create the SQL statement and where you use it.
    I'd rather be riding on the Tiger 800 or the Norton

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
  •