Code Samples for Businesses, Schools & Developers

First Published 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

NavPane 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

Ribbon Menu
Query Design View Context menu

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


      Typical Usage:

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

'change to SQL view

2.   Design view to Datasheet view


      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

Design to SQL or Datasheet
      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:

Query Metadata

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



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
            Wait 3
            'change to SQL view and wait 3 seconds
            Wait 3
            'change to datasheet view and wait 3 seconds
            CommandBars("Query SQLTitleBar").Controls(2).Execute
            Wait 3

            '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


End Sub


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 31 July 2023

Return to Code Samples Page

Return to Top