First Published 29 Oct 2023 Last Updated 30 Nov 2023
I sent this feature request to the Access team on 31 July:
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.
There are of course ways around this including the use of command bars code. For example:
'open a selected query in design view
DoCmd.OpenQuery "YourQueryNameHere", acViewDesign
'change to SQL view
However, doing something like this certainly isn’t obvious to most Access users.
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.
This new feature was rolled out to the Current Channel in version 2311 on 29 November. The change affects Access 2016/2019/2021 as well as 365.
In the updated version, queries can now be opened directly in SQL view in 3 different ways:
1. Right clicking on a query in the Navigation pane and selecting SQL View
2. In VBA code by writing
DoCmd.OpenQuery "Query Name", acViewSQL
3. Using a macro with the new SQL View option
The existing navigation pane menu items still work exactly as before:
a) Open - opens a query in datasheet view
b) Design view - opens a query in the last saved view (Design or SQL) except for SQL-only query types
The new SQL View feature will be a huge time saver for those who regularly view/edit queries in SQL view rather than the query build editor (QBE).
I would like to publicly express my thanks to members of the Access team for responding to this feature request so quickly.
It was somewhat ironic that when the feature was released to the Beta channel in version 2311, I was able to use a macro or VBA code to open a query in SQL view, BUT the new navigation pane shortcut menu did NOT appear on two of my workstations!
When it still didn't appear in the following update (version 2311 build 17022.20000), I first tried repairing then removing and reinstalling Office 365.
I also tried manually editing the shortcut menu using Dale Fye's excellent Access Shortcut Tool add-in.
Unfortunately, none of those had any effect.
I then discovered a registry key HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Settings\CommandBars which contained information about the various command bars used to populate shortcut menus.
In my case there were well over 200 entries.
Following a prompt from a member of the Access team, I exported the registry key to a text file then deleted the entire key in the registry.
On restarting Access, the new SQL View menu appeared and worked perfectly.
NOTE: A new registry key was created for that command bar when it was needed.
Other command bar entries were added 'on demand' as I used certain other Access features. This ONLY happens when a command bar is altered.
It would appear that the old registry entries had an unwanted side effect of 'locking in' those command bar menus so the version 2311 update had no effect on the nav pane shortcut menu.
I hope the above information helps others who may experience the same issue.
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 30 Nov 2023
Return to Access Blog Page
Return to Top