DAO ADO and Query for searching records

[TioAdjie]

Dear Access Master,
I have the question,
1. For searching the records, which is faster whether use ADO or DAO ?
May I know a little syntax ADO or DAO ?
2. Do we have other alternatif searching the record such as using Query ? If there is, may I have syntax for searching the record by using Query ?

Thanks a lot for your help.
regards,
Tio

[ItsMe]

SELECT queries are useful. DAO is a nice tool. ADO is a great help when you need to connect to an external data source.

[TioAdjie]

According to the experience, which is faster for searching the records ?.

Thanks
Tio

[ItsMe]

SQL may be the best performer. It really depends on how you write your code and the ultimate judge on the speed of your database is how you structure your tables. If you start with bad table design you will have bad performance, regardless of your subsequent efforts.

[TioAdjie]

Ok. Let's think that table design is already good. You said that SQL is best. What if the query syntax that converted into SQL languange will be the best also ?

[Xipooo]

I personally prefer ADO, though nearly everyone knows by now Microsoft emphasizes DAO and is likely to discontinue support for ADO. For that reason alone, I suggest that you stick to DAO.

[TioAdjie]

I'm sorry to continue this question. I have been told before that query is faster than DAO/ADO, do you have the same opinion with that ? If so, could you please give me a little syntax how to search the record by using query ?

Thanks a lot and regards,
Tio

[Xipooo]

You are mixing two different concepts.

ADO and DAO are technologies that connect Access to a back end database like MS SQL, Oracle, MySQL, etc..

Queries are a block of SQL Language code sent to SQL servers to manipulate data in some way. Either Create, Read, Update, or Delete data (CRUD). You can also perform other functions for management and maintenance of a SQL server using SQL code, but for the purposes of what we use Queries for it is just to move data around.

An analogy would be imagine if you wanted to get to the post office to get your mail. You have several different ways to get there. You could walk, ride your bike, drive, or take public transit. Perhaps even a combination of those things. That is DAO and ADO. They are the provider. They provide a way for you to go interact with your data.

Now once you are at the post office, you will need to communicate with the post office attendant what your intentions are. The language I speak to my attendant is English and hopefully that is the language they understand. SQL server speak in the "SQL Language". If I were to write down my request on a piece of paper and hand it to the attendant that would be a "Query". One block of writing which indicates my intentions. Based upon what I've written down the attendant will know to either send a piece of mail, or hand me my mail.

"Pass-through" queries are basically what I've explained here. However, a normal query in Access uses an interpreter to communicate with the SQL server for us. Sort of like if the attendant at the Post office and I spoke different languages, we would need a 3rd party to act as an interpreter for us. This is why "Pass-Through" queries are faster than standard Access queries. There is no need for an interpreter.

[ItsMe]

Xipooo I do not understand your analagy and the comparison of ADO to DAO. MY understanding is that DAO is native to Access and provides access to objects within the ACE model (previously JET). So if you are trying to connect to another Access DB through an ODBC connection, DAO would probably be preferred over ADO because DAO should provide additional functions over ADO. If you are trying to manipulate an object outside of the model then ADO may be the better choice. I prefer to use DAO when I know I will be working with Access files like accdb, mdb, accde, mde, but not project files (regardless of ODBC connections or not).

As for DAO being discontinued, I have not seen any evidence of this. When DAO was integrated with the ACE engine for A2007, DAO was enhanced and its library is referenced by default.

I do not know why the OP is asking what is fastest. I thought I answered the question by stating that speed is in your design.

[Xipooo]

Xipooo I do not understand your analagy and the comparison of ADO to DAO. MY understanding is that DAO is native to Access and provides access to objects within the ACE model (previously JET). So if you are trying to connect to another Access DB through an ODBC connection, DAO would probably be preferred over ADO because DAO should provide additional functions over ADO. If you are trying to manipulate an object outside of the model then ADO may be the better choice. I prefer to use DAO when I know I will be working with Access files like accdb, mdb, accde, mde, but not project files (regardless of ODBC connections or not).

As for DAO being discontinued, I have not seen any evidence of this. When DAO was integrated with the ACE engine for A2007, DAO was enhanced and its library is referenced by default.

I do not know why the OP is asking what is fastest. I thought I answered the question by stating that speed is in your design.

He was confusing queries with providers. I was trying to explain those differences, not the difference from ADO to DAO. Here's what he said:

I have been told before that query is faster than DAO/ADO, do you have the same opinion with that ?

Also, I said ADO was being phased out... DAO is the emphasis now from Microsoft.


I prefer ADO simply because I have found it somewhat easier to build a class object to instantiate and work with for direct communication with my SQL servers. However, it could be revised to work with DAO as well.

I avoid using standard Access Queries that use linked tables to other database systems like MySQL, MS SQL, Oracle, Postgree, etc. This is due to the interpretation that must be made between the access JET(ACE) engine and the SQL server itself. Although it certainly makes life easier to write an Access query to work against a linked table, the speed can be dramatically affected due to this conversion ... especially when you have to combine data from two different sources.

When you execute a Pass-Through query the SQL server you connect to will be executing the request and return the results back to you. However, when you run a stored query in Access against a linked table, the JET (ACE) engine must take your ACCESS query and alter it's language to match that of the SQL server. As such there are certain generic data requests which can sometimes pull back more data than is necessary and is unfiltered.

If you are simply connecting to an Access BE, then linked tables should work just fine with standard Access queries. But overall the sooner you can learn to work without the crutch of the JET (ACE) engine, the better off you will be both in terms of future development, but also speed and efficiency.

[ItsMe]

.. I was trying to explain those differences, not the difference from ADO to DAO...

....Also, I said ADO was being phased out... DAO is the emphasis now from Microsoft....

Well, there you go. I really did not understand your analogy and I was imposing my view of DAO vs. ADO upon your analogy. I don't want to derail this thread more than it already has been.

[TioAdjie]

Xipooo, thanks a lot for your answer. It's really helpful. I can conclude that it's different concept. And I have just known that pass-through query is faster than standard query. I want to learn pass-through more deeply. Again, it's really helpful. May God bless you for your help.

[TioAdjie]

Its Me,
Thanks for your answer. Now I will be more deeply to design the table especially normalization, so it will speed database. Thanks again.