MS Access's ACE SQL engine continues to run with a variant form of ANSI-89 with some ANSI-92 features while the rest of the relational database world has adopted more recent ANSI standards and at the very least fully comply to the universal, lowest common denominator standard accepted in the industry: ANSI-1992.

Being a highly used, worldwide database for small to medium-sized shops and its popularity with the Office suite, enhancing Access's dialect to industry standards will add value to current and future users including the Microsoft brand. Even SQLite, a similar, very popular file-level database as open-source counterpart to Access, has a richer, more expressive updated SQL dialect.

Such specific features that would greatly enhance the expressiveness, portability, and efficiency of Access queries would include:

ANSI-1992

- Support of other joins - FULL OUTER JOIN, NATURAL JOIN, and CROSS JOIN; with use of USING clause;
- Support of CASE statements as more enhanced, portable version than IIF() especially for nested logic;
- Support of distinct aggregate functions: COUNT (DISTINCT ...), AVG (DISTINCT ...), SUM (DISTINCT ...);
- Support of wildcards operator, %, in GUI rather than * or use of ALIKE or restricted to ODBC/OLEDB queries;
- Support of CAST for data type changes as opposed to the non-portable C-functions: CStr, CDbl, CLong, CDate, etc.;
- Support of COALESCE for handling of NULL across columns as opposed to nested IIF(..IS NULL) or NZ();
- Elimination of the parentheses requirements for more than one JOIN which often frustrate many new users and require the use of Access GUI's Design View to build complex queries;
- Support of ON clause conditions on single table fields rather than joining tables first and then running WHERE.

Other ANSI

- ANSI-1999: Common Table Expressions (CTE) using WITH clause for more readable, maintainable, and less repetitive SQL code than subqueries and derived tables (supported in SQLite as of version 3.8.3, released 2014-02-03);
- ANSI-2003: Window functions using OVER and PARTITION BY clauses to supplant the domain (DSum, DMax, DCount, etc.) functions with more efficiency than correlated subqueries (supported in SQLite as of version 3.25, released 2018-09-15);
- ANSI-2003: Support for MERGE statement for synchronous UPDATE and INSERT needs based on conditional logic.

Other recommendations

- Support of EXPLAIN <QUERY> to allow users evaluate query operations especially use of indexes;
- Support of CREATE DATABASE as opposed to requiring code for creating .mdb/.accdb files;
- Support of CREATE TABLE AS instead of only SELECT * INTO for clarity of DDL action;
- Support of CREATE VIEW AS instead of requiring code with QueryDefs for creating stored queries;
- Distribute the ACE engine (.dll) as a shared object (.so) with CLI (similar to SQLite) for Unix (Mac, Linux) users (e.g., SQL Server 2016/2017 is now supported on Linux).

With backwards compatibility, above changes is a challenge but such features can be integrated with newer versions of the ACE engine (.dll) to be distributed to users, or with add-on patches, or entirely new Access version.

Additionally, though Microsoft strategically intends to keep SQL Server as its flagship database system and upgrading Access may somewhat interfere with this objective, arguably Access can be situated as a complement and not substitute to the enterprise server RDBMS. Rather than advancing Access’ front end GUI features for use as a complement to Excel, consider strengthening MS Access as a standalone, small-scale database engine product.

SQL is something that I find particularly useful when I am running a database. However, the SQL user interface is very poor in my opinion. It should be more something like the Visual Basic interface, where when you start typing in a formula, then it has a dropdown with all your options and when you select the function from the dropdown, it will show you how to build the formula. For me it becomes very time consuming to have to go online to figure out how the function is built. Also it would be great if it can be colour coded. There should also be more SQL functions that should be made available to the user, like in Microsoft SQL.
Also, being unable to comment directly into the SQL code is something I am finding annoying because I would like to be able to explain to future users what certain pieces of SQL code are for. I would appreciate if you could implement these ideas in the next version of MS Office. Thanks!

In Access I often use the criteria Intellisense/Builder to help me get the vba string to drill down to a deeply nested subform. But Intellisense/Builder gives up about 3 forms in. It would be nice to be able to drill down further with the help of this tool.
I am by no means an expert at Access but over time have created several useful tools over the years, with the help of many Access experts on line. I created a very comprehensive vacation calendar used by over 200 people (tied to an sql back end) for my employer prior to my retirement. So, any tools that help ease the burden are always appreciated.

On the off chance that your Outlook OST record has turned out to be broken or unparented due to a few reasons like infection assault, Trojan disease, awful server, Low system property, document assemble activity disappointment, equipment crash or bundle disappointment and you wish to ask back your OST record because of your OST document is in this way fundamental to you, at that point don't freeze you'll have the capacity to endeavor our OST record to PST document gadget bundle furthermore as OST to PST for working environment 2007. In the event that you wish to investigate the work strategy of our OST to PST gadget bundle for Outlook OST to PST for working environment 2007 change then you'll have the capacity to exchange free demo adaptation of OST record to PST document gadget bundle for thirty Email. With the help of ravishing OST specialist program you'll decidedly recapture undecipherable OST go into clear condition. OST to PST extractor program swimmingly convert relocate OST to PST with exact organization. PST, EML, enhancing and markup dialect square measure offered distinctive amid which you will reestablish recouped OST document. "Motivate OST to PST Recovery bundle that profoundly filter out of reach and damage OST messages data then you'll have the capacity to spare OST email into working MS standpoint with another configuration like as:- EML/MSG/EMLX/HTML/TXT/DOC/MBOX and viewpoint profile. This bundle has also capacity to help OST record as:- five.0/5.5/2000/2003/2007/2010/2013 related 2016 moreover in light of the fact that it gives you a development decision to separate noteworthy size of recouped PST document upto 5GB.

Read more information and download free demo version here:- https://www.inspiredatacare.com/convert-ost-to-pst.html