Results 1 to 5 of 5
  1. #1
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340

    Editing a query

    I'm editing the sql for a query e.g


    Set qq = CurrentDb.QueryDefs("Query1")
    sql2 = qq.sql




    I change sql2 then


    qq.sql= sql2 and run it. It executes as expected.


    But I find this rewrites Query1, but that is not what's wanted.
    I want to run the edited query once but also keep the original.


    How might this best be done ?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Copy the query, edit copy and delete copy (or leave it there to be overwritten)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would probably miss out the middle man in that case

    Code:
    Set qq = CurrentDb.QueryDefs("Query1")
       sql2 = qq.sql
    
    
    ' '   I change sql2 then
    
    currentdb.execute sql2
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Why edit the query and not simply run the SQL expression from your code without using a saved query?

  5. #5
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    Most probably because I don't know how to, and I also got "Can't execute a Select Query" error.
    It was a Union query being changed to include only the tables set in an Option Group.
    Initially I removed those not wanted, but this didn't work as the field names weren't identical for all.
    So now I build a new query in VBA. It may not be the ideal way but is working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Editing Linked Databases via Query
    By zkrucz in forum Access
    Replies: 11
    Last Post: 06-15-2020, 12:27 PM
  2. Editing entries in a query
    By toothpaste in forum Queries
    Replies: 3
    Last Post: 02-17-2017, 10:39 AM
  3. Lock a query for editing - run only
    By paul83 in forum Queries
    Replies: 3
    Last Post: 06-14-2016, 10:30 AM
  4. Editing query results, then appending to a table
    By Access_Novice in forum Queries
    Replies: 2
    Last Post: 03-28-2016, 08:55 PM
  5. Replies: 3
    Last Post: 10-19-2013, 10:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums