The recent Access updates that broke the most simple update queries was bad. However, Microsoft's response was even worse. Rather than Microsoft fixing the very serious problem they created, they said we should change the way we update records in our applications. What moron thought that was an appropriate response? Like many other throughout the world, we have a production system with 400K lines of code and you want us to rewrite every update statement because you do not want to roll out a bug fix? Then, someone thought it was a good idea to wait 30 days to fix the problem. What are people to do in the meantime? These are production systems that people rely on every day to do their jobs! Again, what tone-deaf moron at Microsoft thought this was an acceptable response? Clearly someone needs to be fired.

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.

I frequently run up against the 2048 handle limit (and from the online searching for how to resolve this, it's clear that I'm not alone). PLEASE INCREASE the number of object handles available to Access.
I'm supporting a reporting database that links to poorly designed tables (survey data in hundreds of fields that should be stored as records) in a vendor database that I have no control over. To make the data usable, I'm having to restructure the data, appending multiple union queries in order to get the data into a vertical format needed for our reporting needs. If I could modify the source system, I wouldn't have this problem, but many of us are supporting systems and databases that someone else created or has control over.

When I use a subquery in a query, I expect the outer query to only work on the results of the subquery. However, today, it appears that the outer query (the WHERE clause in particular), is not being strictly run on just the results of the subquery. Instead, the join in the subquery appears to be being ignored, and the WHERE clause is run on the much bigger dataset of the subquery minus its join. This is counter-intuitive and probably incorrect. Please fix this. I had a subquery that only returned two rows, but it appeared Access was performing an outer query function on the subquery without its join which happened to be millions of rows--it was very slow.