First Published 31 July 2023                 Last Updated 9 Apr 2024



UPDATE 9 Apr 2024

I wrote the article below on 31 July 2023 and sent a feature request to the Access team the same day:

Why isn't it currently possible to open queries direct in SQL view from the Access user interface?
I have never understood why this wasn’t built into Access from the start.

Users can open queries in Design or Datasheet view from the navigation pane or using DoCmd.OpenQuery
Once open, they can use the ribbon or context menu to switch to SQL view.

However, queries cannot be opened directly in SQL view UNLESS:
a) it is a SQL-only query (union, passthrough or data-definition)
b) it was last saved in SQL view

Why does it have to be a two step process? It’s a PITA if you need to get to SQL view regularly.
Please can opening in SQL view be made available when opening queries from the navigation pane or in code.

My email got a rapid response from the Access team and the feature was rolled out to the Current Channel in version 2311 on 29 November 2023.
The new feature is available in Access 2016/2019/2021 as well as 365.

For more details, see my article: Open Queries Directly in SQL View

However, older versions prior to 2016 that are no longer supported did not receive the update.
The information below should therefore be used if you are using Access 2013 or earlier versions


ORIGINAL ARTICLE 31 July 2023

It is often very useful to change query views using code.

For example, when opening a query from the navigation pane or using code, it would often be useful to go directly to SQL view.
However, Access only allows you to open the query in datasheet view or design view.

Navigation Pane Context Menu

Once the query is open in design view, you can then switch to SQL view e.g. from the ribbon or a right click context menu. . .

Ribbon Menu

Query Design View Context menu

However, doing that repeatedly soon becomes annoying and has been a long term irritation of Access for me

Luckily, it is easy to do this in one step by utilising the command bars used in the various query view right click shortcut menus

The only problem is knowing the correct code to use in each case. This article provides all you need to know!



1.   Design view to SQL view

CommandBars("Query").Controls(1).Execute

      Typical Usage:

'open a selected query in design view
DoCmd.OpenQuery "YourQueryNameHere", acViewDesign

'change to SQL view
CommandBars("Query").Controls(1).Execute

2.   Design view to Datasheet view

CommandBars("Query").Controls(2).Execute

      How does the code work?

      a)   The command bars named in each code line above are the name of the built-in right click context menu - in this case 'Query'
      b)   The Controls.(Number).Execute tells Access to run the numbered item in that menu: (1) = SQL view; (2) = Datasheet view


      Similar code works for the other view changes using the relevant CommandBars context menu:

3.   Datasheet to design view

CommandBars("Query Design Datasheet").Controls(1).Execute

4.   Datasheet to SQL view

CommandBars("Query Design Datasheet").Controls(2).Execute

5.   SQL view to design view

CommandBars("Query SQLTitleBar").Controls(1).Execute

6.   SQL view to datasheet view

CommandBars("Query SQLTitleBar").Controls(2).Execute

I use all the above code in several of my applications including the Query Metadata Viewer
This allows you to select any of the 3 views for selected queries at the click of a button:




You can even use code to toggle through the various views in sequence      

       

CODE:

Sub QueryViewLoop()

      Dim i As Integer

      For i = 1 To 2
            'loop twice though query views at 3 second intervals
            'open in design view and wait 3 seconds
            DoCmd.OpenQuery "YourQueryNameHere", acViewDesign
            DoEvents
            Wait 3
            'change to SQL view and wait 3 seconds
            CommandBars("Query").Controls(1).Execute
            DoEvents
            Wait 3
            'change to datasheet view and wait 3 seconds
            CommandBars("Query SQLTitleBar").Controls(2).Execute
            DoEvents
            Wait 3
      Next

            'close without changing the saved view
            DoCmd.Close acQuery, "YourQueryNameHere", acSaveNo

End Sub

You also need to save the following code in a standard module to set the wait time in seconds

Option Compare Database
Option Explicit

'========================================
'API declarations
#If VBA7 Then       'A2010 or later (32/64-bit)
      Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else       'A2007 or earlier
      Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If
'========================================

Sub Wait(N As Integer)

      'creates a delay while other code executes
      'N = number of seconds to wait
      Dim i As Integer

      'loop using sleep function from api library file
      For i = 1 To N
      'pause 1 second (1000 milliseconds)
            Sleep 1000
      Next

      DoEvents

End Sub

Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 9 Apr 2024

Return to Code Samples Page

Return to Top