First Published 29 Oct 2023                 Last Updated 9 Dec 2023

On 31 July 2023, I sent this feature request to the Access team immediately after writing my article: Change query views using command bars code

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
CommandBars("Query").Controls(1).Execute

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



NOTE:
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.



ADDENDUM:

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.



Update 6 Dec 2023

After the update was released, I realised that there was still one area that had been omitted from this new feature.
In the past few days, I have asked that a new Query SQL View item be added to the Create ribbon.


The idea is that clicking the button in the Create toolbar would open the SQL view with SELECT; already entered:


This would be exactly the same as when you swop from query design to SQL without first adding a table or query to the query build editor (QBE)

My latest request is currently being considered by the Access team and I hope to report back with more news in the near future.



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 Dec 2023

Return to Access Blog Page

Return to Top