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: