Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Question about macro running query! (Access XP)

    If a macro is to run a query, a person would choose the macro action: RunSQL. Then they need to paste the SQL of the appropriate query into the SQL macro arguments area of the macro window. Many times it throws the error msg: The text is too long to be edited!
    If I have a large query, I cannot shorten out the SQL...!! What can I do to have a macro trigger the query then?

    PS: I also have issues when I need to paste it into a module. Then you have to fuss with syntax as the SQL is over multiple lines and VBA does not like this!!

    What can I do to allow macros to run queries? Any advice will be appreciated. Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Question about macro running query! (Access XP)

    Why don't you use a stored query and the OpenQuery action? If the query is an action query, OpenQuery will execute it. The VBA equivalent is DoCmd.OpenQuery.

    When you need to create a long SQL string in VBA, you can use & and the line continuation character _ to split the string:
    <code>
    strSQL = "SELECT Field1, Field2, Field3 " & _
    "FROM tblTable " & _
    "WHERE Field1 > 3 AND Field2 Is Not Null " & _
    "ORDER BY Field3"
    </code>
    If you copy an SQL string from a query to the clipboard, you could paste it into Word or Notepad first, and process it there before pasting the result into a module, to avoid the VBE error messages.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Question about macro running query! (Access XP

    The maximum string length for an SQL query is 65,000 characters (according to <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26> in <post:=485,239>post 485,239</post:>).

    By the way...
    <hr>[sillyme]<hr>
    - don't you mean <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>?
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Question about macro running query! (Access XP)

    I was so focussed on RunSQL, that I never even noticed OpenQuery. (Of course this will run as query!!! - [sillyme])
    Tx for the advice on the long SQL statements. Out of interest, is there a maximum limit in the length of the SQL string that can be added to the RunSQL argument?
    Cheers
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Question about macro running query! (Access XP

    Tx Waggers,

    I don't like that smiley.... it gives me a headache!!!!!!!

    Cheers
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Question about macro running query! (Access XP)

    Also out of curiosity, is it possible have docmd.runsql run a query made of several SQL statements? I'm not sure the wording is 100% accurate, but I mean running a query like:

    <font face="Script MT Bold">update TABLE set FIELD1 = value1 where FIELD2 like '1'
    update TABLE set FIELD1 = value2 where FIELD2 like '2'
    ...
    update TABLE set FIELD1 = valueN where FIELD2 like 'N'</font face=script>

    The query gets as many SQL statements as items I select from a multiple-selection listbox. If I select only one item and then click on the button that first builds the strSQL string and then uses docmd.runsql(strSQL), the query runs okay and the field is updated. If I select more than one item in the listbox, I get runtime error 3075 which translates to something like:

    "Syntax error: missing operator in the query expression".

    Even a 2-statement query will trigger the error, so it's not a matter of the lenght of the string either.
    I tried running the same multi-statement query manually in the SQL View of a new query but Access doesn't seem to like it that way either.

    I have found a workaround at VBA level by querying Access N times (one for each SQL statement). In fact that's how I'd done it in the first place, but I thought that maybe running one single query would be more efficient.

    SQL Server can deal with this type of queries, but what about Access? Can something be done with the SQL code?

    Thanks!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Question about macro running query! (Access XP)

    Access can only process one SQL statement at a time, so you'll have to execute DoCmd.RunSQL for each SQL statement separately.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Question about macro running query! (Access XP)

    Perfect. Thanks, Hans.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

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