excel - Difference between ADO and DAO

Question

This is not a question about which is better, but rather a question regarding why they differ functionally. The problem I was running into has been handled, but I am curious as to why this behavior is happening.

Background - using Excel vba to pull data from an Access database. When user clicks a button, a recordset is pulled from Access, and it populates various data to the spreadsheet. Then, another recordset is pulled from a different query to populate another part of the spreadsheet.

What ADO does - ADO works great for my first recordset. However, my second recordset goes to the query in Access, runs, and returns no rows. If I run this query in Access, it does open up (after about 3 to 4 seconds). This query has multiple joins, computed items, limits, and possibly Union queries (I tried it many different ways, with/without union,etc.). I tried closing and reopening the ado connection. I tried changing timeout values, and I even tested using an ADO command to run Make table queries for this data, and then pull from the table instead (this worked by the way, but is not the best-case, since the data changes continually, and I do not want to have to run the make table query everytime someone uses this tool).

So, I changed the second data pull to DAO, and lo and behold, it works. The first data pull is still ADO (which I generally prefer to use), but am now considering changing it to DAO, because I would rather have one data access method in the code.

So, can someone explain to me why ADO will not pull the data in one case, but DAO will? Again, this is purely for informational purposes.

Answer 1

DAO is the native data access method for the Jet (Ms-Access) data tables. ADO "Active X Data Objects" is an industry friendly connection to almost all types of database.

With a standard query there is no reason in this case why ADO should return no records where DAO does, I suspect it's that the query must also contain parameters relating to items within the Access database. If this is the case then ADO will not work as it will not have the option of using said parameters as it is just an external reference to Excel, using the DAO method will trigger Access to run the query rather than Excel and as such it will be able to access it's own parameters/references.