On my Access with SQL Server group at LinkedIn, one of our members posed the follwoing question: ADO or DAO for new development work?
Let’s break it down
The question implies DAO or ADODB are mutually exclusive, but they are not. You could easily create an Access app using just DAO that would work just fine, but you would be hard pressed to create the same app using only ADODB, it would take a lot more code and too many compromises along the way. The fact remains that DAO is and will continue to be the default technology to be used in everything Access.
So is DAO only the way to go? No, use ADODB to tap into SQL Server directly
If you wish to optimize the relationship between Access and SQL Server, than you MUST use ADODB in order to obtain the greatest efficiency/ROI in your code. The trick is knowing when to use DAO and when to use ADODB.
Why is ADODB faster than DAO?
Again, this is the wrong question, rather, why is SQL Server faster than Access should be the question. If you ask Access to update 100,000 records using a native Access update query vs using ADODB, be prepared to have one or two cups of coffee while you wait. In general, if your process will run faster on the server, (and they almost always do), then use ADODB, otherwise use DAO.
Pick the one best suited for the job
Here is a list of situation where you should use DAO:
- Data source for Forms and Reports
- Local queries of Linked tables, (although to the extent possible use views if your query has more than one table in it)
- Mixed queries that have local tables and linked SQL Server tables, (avoid this scenario at all costs unless you also enjoy watching grass grow and paint dry)
- Tabledefs and Querydef code in your app
- When you need to download data into temp tables in Access
- When you need to use pass through queries for your reports or forms in read only mode and you don’t need to edit the data.
- When you need to work with local tables.
Here is a partial list of where you should use ADODB:
- When you need to tap into SQL Server data inside your code with recordsets
- Inserting records via code
- Running action queries
- When you need to upload data via XML data
- Generally, if you can pick between using DAO, (linked tables) or ADODB, (direct to SQL Server) than use the latter.
This is only a partial list, there are many more examples of when you should use DAO or ADODB, but the list does cover a wide patch of situations you will come accross when trying to optimize Access with SQL Server.
Take a look at these posts to learn more about this topic:
- Go to AccessExperts.com/starthere for a video and a list of posts to help you get started on SQL Server with Access
- Go to AccessExperts.com/easy for a great methodology on using ADODB with ease in Access.
Hi,
https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/
taks about “anti-depreciation” of ADO driver
Does that mean, the using of ADO in Access SQL Server is “safe” now ?
I didn’t use because of depreciation … although i’d liked to do it …
Juan, I have asked about the advantages of ADO vs DAO, and I have had specially two answers that says that I shouldn’t use ADO because is deprecated. Can you give me your opinion about this? The discussion is in:
https://www.linkedin.com/grp/post/132455-6027862239166369792#commentID_discussion%3A6027862239166369792%3Agroup%3A132455
First of all, it’s technically incorrect to say that ADO is deprecated.
What WAS deprecated is the OLEDB provider for SQL Server as a first-class provider since SQL Server 2014 and onwards. (2012 is the last version that will ship with a native client that also includes the OLEDB provider). Because a large majority of ADO code used in Access database are likely to be against SQL Server, this can be a big deal. But ADO itself is still a part of current data access technology.
If the worst thing happens, what one could do is just change the provider from SQLOLEDB/SQLNCLIXX to MSQLDA (aka OLEDB provider for ODBC) which will then use ODBC to connect to SQL. The downside is that you now have more layers (e.g. VBA -> ADO -> OLEDB -> ODBC -> SQL Server) but at least you would be able to continue to leverage features in ADO that are not available in DAO.
If you don’t already have a codebase or library of functions that uses ADO, I would probably just do everything DAO alone but do consider that when you get into some more complicated process, it is much harder with DAO. A great example is streaming BLOBs. That is possible in DAO but not as straightforward as doing it with ADO.
HTH.
The Access tied to SQL Server and distributed via Citrix lowers the bandwidth overhead and the need for each client to manage licensing. Bandwidth between Access and SQL Server are contained at the server site. The rich-user front end can typically be used with the old 1,200 baud dial-up. No pesky java of the week downloads. It works with both PC and Mac.
Access is still one of the best tools to prototype and get a product to market quicker, with a very rich user interface.
Why Access was never mentioned in http://greatcustomsoftware.com/services/ ? is Access deficient as a custom software db devt. tool?
Juan, I love Access and SQL Server (and reading your blog) but the direction Microsoft has taken both has caused me to give on Access. SQL Server will likely be next.
I’ve used Access/SQL Server for groups with little resources. It’s ideal for that or it was. You could give someone a front-end they could modify and back-end that is rock solid. Development was quick which is nice when you are doing volunteer work. ADODB helped a lot when the SQL Server was a couple of thousand miles away.
It comes down to a few issues:
* The draconian licensing Microsoft issued with Office 2013 was unusable by groups unable to afford Office 365. The it-dies-with-your-computer license did not help. I know, Microsoft backed off but it’s too late now.
* The end of OLEDB means the end of ADODB. It’s not likely that support or enhancement of MSDASQL will continue either.
* Web databases require SharePoint. Either you buy it or you use Office 365. And you can’t use VBA, just macros.
That’s a joke compared to what you can do with JavaScript and frameworks like Ember.js or Angular.js and back-ends in PHP or Rails with MariaDB or Postgres. Reports are not a good as SSRS or Access but they’re good enough with CSS.
So I’ve moved on. But I’ll always have a soft spot for Access.