Results 1 to 5 of 5

Thread: Is it possible to look at the SQL of an Access query without running it?

  1. #1
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0

    Is it possible to look at the SQL of an Access query without running it?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've started using Access, and finding it very poorly designed and badly documented.

    My previous experience was on SQL Server, where you can of course view the SQL of a query and the query results at the same time. Not only does it look like you can't do this in access, but also whenever I switch between SQL view and Query view it retriggers the query. ARGGGGG!!!

    In fact, I have several queries that I really really do not want to run, but really really want to view the SQL. But I can't work out how.

    Can anyone enlighten me as to whether this is possible?

    I'm on the verge of just ditching Access altogether in favour of SQL Server. Only thing holding me back is that Access is more widely available to my users.

  2. #2
    The specific instructions depend on your version of Access. In 2003, right click the query in the database window and choose "Design View". Then go to View > SQL view to see the SQL.
    Regards,
    JP

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Hi JP. Sorry, forgot to give the version. I'm using 2010 at home (where I am just now) and 2003 at work. In 2010 the database window has been replaced with the navigation pane, and the VIEW opiton is in the ribbon. While the View option has a dropdown from which you can select SQL view, you have to first run the query before this becomes selectable. If you haven't run the query, then the View option is grayed out.
    You don't have access to the View option by right clicking.

    I'll try your suggestion on 2003 in a couple of hours when I'm at work, but I swear that I had much the same problem there, in that yes you can view the SQL but not without running the query first, and if you want to then switch from the SQL view to look at the results for just a quick peek, the query automatically refreshes.

  4. #4
    I am able to do it without actually running the query. If you switch from SQL view to 'Datasheet' view (which is the result of the execution of the query) then yes you actually have to execute the query

    You could also use VBA to view each query. See http://www.codeforexcelandoutlook.co...ries-database/ for a way to view each query's SQL statement in the Immediate Window. That code may not work in Access 2010, however.

    If Access 2010 forces you to run the query before you can view the SQL, that is terrible.
    Regards,
    JP

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi Jeffrey,

    I can do it without executing the query in 2010:
    • Find the query in the navigation pane
    • Right click and choose Design View
    • On the Design Tab change it to SQL View
    That doesn't execute the query as you never get to Datasheet view

    Seems to me that someone might want to make an addin for Access that adds "SQL View" to that right click menu!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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