Difference between ADO and DAO
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.
I think this question was answered very well already but I want to add another excellent (although somewhat hard to find) resource for comparing DAO and ADO. It's targeted primarily at Access applications with Access forms and reports, but much of the information here would apply to a solution written in Excel.
In Visual Basic, three data access interfaces are available to you: ActiveX Data Objects (ADO), Remote Data Objects (RDO), and Data Access Objects (DAO). A data access interface is an object model that represents various facets of accessing data. Using Visual Basic, you can programmatically control the connection, statement builders, and returned data for use in any application.
Why are there three data access interfaces in Visual Basic? Data access technology is constantly evolving, and each of the three interfaces represent a different state of the art. The latest is ADO, which features a simpler — yet more flexible — object model than either RDO or DAO. For new projects, you should use ADO as your data access interface.