Make Access a better ODBC client
Given that the trend is toward ODBC and away from OLEDB (ADO), there are number of gaps still present in Access as an ODBC client:
1) support for asynchronous operations
2) support for processing multiple resultsets
3) support for streaming large results
4) support for updating complex data sources (e.g. binding to a stored procedure and then specifying which stored procedure to call to perform insert/update/delete)
and other features that has been added to ODBC (currently at v3.8) but is not available via DAO.
This is necessary to enable Access to transition away from ADPs, better leverage remote data sources and handle more complex structure than just tables and views. Note that in past, Access had ODBCDirect feature which was massively underutilized due to the fact that it was simply not useful with Access forms/reports; and was a code-only solution. A successful solution should be 100% compatible with Access forms & reports.
Sascha Trowitzsch commented
Indeed ODBCDirect had been a highly underestimated feature. IMO that's due the fact that formerly SQL servers were rather expensive and that developers had to much respect too the complexity of SQL servers. So only a few complained when it deprecated.
Times changed and now we have a bunch of free DBMS engines. Not only MSSQL but also Oracle Express, DB2 Express, MySQL/MariaDB, Postgres, Firebird,... and maybe SQLite, some of them being rising stars if you look at db-engines.com. I personally use MariaDB for the backend as it also runs on LINUX.
ADODB is no solution because many DBMS don't offer appropriate OLEDB Providers (unstable, unperformant) and the MS-ODBC-Provider is a senseless bottleneck.
MS took off the Access Projects and recommended ODBC as an interface while making to handle this more difficult in removing ODBCDirect at the same time. I never understood that.
So I would vote for bringing ODBCDirect back in a first step and further adjust that interface in ACEDAO to a more modern state.
Mark Jaskula commented
What I'm doing is creatng a VB.Net DLL and adding it as a reference to the Access DB. Create and I/O interface, and get all the riches of Visual Studio with Entity Framework, parallel processing etc.
No brainer consider the snail's pace progress of fundamental MS Access functionality.
Add "server filter" capability like we used to have in ADP!!!!!!
server filter reduce network traffic by a factor of 1000 - that's why ADP was so good
allow forms to filter at server and not bring an entire table to client then apply filter
does not much sense to understand that!!!!
Here's a bunch of compelling reasons why Access should update its ODBC support:
Imagine Access being the first kid on block to support web authorization. See it handle JSON or CSV more effectively than it can. A bunch more....
Patrick Headley commented
There is an issue with ODBC databases where Access doesn't generate an auto-number because the server does. If the auto-number is also the PK value, Access can't find it when reading back a new record. Because of that it has to perform a search on all fields. If there is a duplicate record except for the PK field, the wrong record can be returned on the read-back.
Is there any way that Access can deal with this automatically? I already know of three options to deal with it myself.
OMG, I didn't even add true in-memory/disconnected recordsets to the original list! Yes, that's definitely something else. With DAO, you sort of work around with temporary tables but that's LOT of coding, not to mention additional coding just to sync back the changes. That might be implied with #1 (async operations) but disconnected recordsets --- especially when it works with bound forms/reports --- would help enable many more scenarios without bloating the front-end.
Yep, DAO has a lot to catch up. Time to 1+ up!
Mark Burns commented
If they do make Access(/DAO?) a "better ODBC client" I would hope that they also find a way to re-create the capabilities of ADBDB disconnected (in-memory only) recordsets - including chaptered/hierarchical recordsets. I know - "why re-invent the ADODB wheel?" right? Well, because they're basically throwing the old ADBDB wheel away, and those libraries, as useful as they are/were, are simply going the way of the Dodo over time. So, if increased ODBC capabilities are to be considered, then this set of feature requirements deserves a seat at the negotiating table as well.
Just to offer few user stories for why this feature would be helpful:
Imagine wanting to be able to add a new invoice, which normally means entering a record in the invoice header, then the line items as several records on the subform. Now imagine wanting to cancel the invoice.
With the bound forms, it is next to impossible to do this transactionally where a cancel should just mean discarding the data for both header & details and rollbacking the transaction. One must resort to additional coding (wait, we use Access to write LESS, not more code) just so that we can ensure that incomplete invoice don't end up saved in the database. Ability to control the transaction across bound forms would help enormously in that respect.
Another example is when there's a slow connection, as soon as an user commits update, the whole form freezes, forcing the user to wait until the update has been committed. Modern applications, (including Access when connecting to SharePoint) allow background update with signal notification to indicate that a update is pending and should signal an error if it fails for some reasons. The more advanced users can use events to trap and deal with it more gracefully (e.g. retrying for transient network fails or to ensure user do not leave the form until all has succeeded). ADO allowed you to subscribe to events directly to the connection or recordset objects; DAO has NO events at all.
Imagine being able to define a recordset and re-use it easily for multiple controls on different forms. Suppose we have a zip lookup functionality. Using a direct query on a combobox can be very expensive since Access query for EACH control separately. Currently this is only possible via VBA coding; not by simply setting a property.
Imagine that in an enterprise setting, there is a tight IT control on a existing SQL Server and the policy is to access and modify data through stored procedure ONLY. Presently, lot of VBA code must be written to "bind" updatable forms to it, in spite of the fact that sprocs are just basically glorified view and one-row modification (remember, IT policy). ADO allows you to define commands. DAO doesn't.
Imagine being able to tell Access how to handle volatile primary key. Prefer sequences? Sure, set a property to get next value. Prefer custom stored procedure? No problem, too. That would cut down on errors causing #deleted or unnecessary queries (sent by Access in background) for figuring out which new record is really my inserted record.
Each user stories on their own, aren't that big in themselves but put together, that is what we get if we were to make Access a better ODBC client, supporting and exposing more features, yielding many low-hanging fruits.
Pat Hartman commented
MS missed the boat when it attempted to respond to requests to make Access web enabled. No one actually wanted MS to develop a whole new (and inferior) FE model. We wanted Access to be a better ODBC client so we could have response times similar to that of web pages. Although Access works well enough using ODBC on a LAN (as long as you use a little common sense), it sucks when the database is somewhere in the cloud. THAT is the problem we need MS to fix. The "Access Way" is bound forms/reports. We need ODBC solutions that integrate with bound objects.
Mark Burns commented
Also - though authoring .ADPs is no longer supported, apparently running them still is. However, this becomes increasingly problematic as the move on the SQL Server side as away from both the old MSDASQL driver and the newer SQL Native Client drivers in favor of the newer-generation ODBC Drivers. I just rant into a situation where an Access 2010 .ADP was needing to be bmigrated form its SQL2005 back-end and we attempted to simply attach the .ADP to a newer SQL 2014 Server in direct replacement. The results were less than spectacular...it went from about 5-10 seconds to open and start the application to 45+ seconds to simply OPEN the database (even longer to auto-start the application). The change was NOT an issue with VBA code that directly opened ADODB connections to the SQL Server and executed data handling issues. The primary issue arose when using the built-in MS-Access 2010 SERVER options to relink the source database form the old SQL2005 server to the new SQL2014 server. This is a grossly insufficient level of performance for newer technology verses the older server technology!
One major omission from the list.... ability to parameterize the pass-through queries / stored procedures just like a native Access query.