Feedback by UserVoice

I suggest you ...

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.

189 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    B. Clothier shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    11 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Sascha Trowitzsch commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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!!!!

      • Patrick Headley commented  ·   ·  Flag as inappropriate

        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.

      • B. Clothier commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        @Ben,

        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.

      • B. Clothier commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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!

      • B. Clothier commented  ·   ·  Flag as inappropriate

        One major omission from the list.... ability to parameterize the pass-through queries / stored procedures just like a native Access query.

      Feedback and Knowledge Base