 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / April 2006ADO VS. DAO
Which one should I use for Access Databases 2000 and up? Which one is newer, better and recommended? This is a relative question. If you are using an Access data project (.adp) for interfacing with sql server you will have to use ADO because sql server does not support DAO. If you are using an mdb then you have a choice between ADO and DAO. My feeling is that DAO is better suited for mdb's since the mdb is Jet based for which DAO was designed, where ADO is more suited for a sql server type of engine. Rich Quick note: I have several Enterprise Access projects (.adp) that use a combination of VBA and ADO for interfacing with Sql Server. You cannot use DAO code in an ADP. You can only read data from sql server tables in an Access ADP with ADO - com ADO. com ADO can also be used with MDBs, but DAO is easier for reading data from Access mdb tables. Although, I use com ADO in mdbs for reading/writing data to Excel files. Plus, from Excel 2000 and forward, there is support for com ADO which is real nice for using properties/ methods within Excel for reading data from an Access mdb tables like Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=;Data Source=" & strPath cmd.CommandType = adCmdText cmd.CommandText = "Select * From tbl1" Set RS = cmd.Execute Range("A1").CopyFromRecordset RS '--also works great with Sql Server tables - just different connection string For .Net project (VB, C#) you use ADO.Net. The big difference between ADO.Net and com ADO is that ADO.Net is object oriented and you can do stuff like this (for sql Server): Imports System.Data.SqlClient (VB.Net) Use System.Data.SqlClient (C#) .. Dim cmd = New SqlCommand("Select * From tbl1, conn) or for Access it would be Imports System.Data.OleDb (VB) Use System.Data.OleDb (C#) .. Dim cmd = New OleDBCommand("Select * From tbl1, conn) And a quick plug for VS.Net - VB still uses a lot of classic VB syntax, but a lot of statements are interchangeable with C#. The big difference between C# and VB.net is that C# uses a lot of Brackets{} and semicolons. Otherwise, they have the same amount of functionality, both use delegates, interfaces, and the rest of OOP stuff. C# is just a little bit lower level - a little more granular than VB.Net (you can fine tune a C# app a little more than VB.Net). Just remember, that the thing with VB/VB.Net is RAPID APPLICATION DEVELOPMENT. There is nothing faster than any form of VB for rapdid application development - asp/aspx, exe, dll projects, interfacing with Sql Server/Oracle/Mainframes... There is no more ADODB in ADO.Net Rich Rich P wrote in message <yxm0g.72$DX.17705@news.uswest.net> : > You cannot > use DAO code in an ADP. You can only read data from sql server > tables > in an Access ADP with ADO - com ADO. I agree that ADO is perhaps more suited for ADPs, but aren't you a bit too categoric here? I mean, the CurrentDB method isn't available, but I think you can do something like the below with the Nortwhind sample database within an ADP, just reference the DAO library dim db as dao.database dim rs as dao.recordset dim l as long set db = dbengine.opendatabase("", dbDriverNoPrompt, false, _ "ODBC;DSN=Northwind;UID=;PWD=;Database=NortwhindCS") set rs = db.openrecordset("select * from employees", dbopendynaset, _ dbseechanges) ' loop the recordset ...  SignatureRoy-Vidar Well, I guess if you work it, then there is a way to use DAO code in an ADP. But why go through the hassel when you have the CurrentProject.Connection object which works with com ADO very nicely? The idea that I am trying to point out is that the later versions of Access have been developed to do 2 things now 1) be a desktop RDBMS (mdb), 2) be a front end interface for an Enterprise RDBMS like sql server (adp). DAO is for the mdb. ADO is for the ADP. And ADO.Net is for VS.Net. These technologies all have their place. Yes, com ADO and DAO are interchangeable in the Access environment where ADO.Net is exclusive to VS.Net. And the beauty of ADO.Net is that now you have Non-Persisting tables in memory that don't require I/O disk reads. Now you have real performance (with ADO.Net) I guess my answer to the original post, based on the information provided, would be to experiment, and read a lot of books to be familiar with the technologies. Rich > The idea that I am trying to point out is that the later versions > of Access have been developed to do 2 things now 1) be a desktop [quoted text clipped - 3 lines] > place. Yes, com ADO and DAO are interchangeable in the Access > environment This is simply *not* true. DAO is superior to ADO when using Jet data. It's not a matter of whether it's an ADP or an MDB, but of where your data is stored. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ << This is simply *not* true. DAO is superior to ADO when using Jet data. It's not a matter of whether it's an ADP or an MDB, but of where your data is stored. yes. I am in complete agreement with you that for Jet, DAO is definitely the way to go. If you notice that in all of my posts where I reference currentDB in example code, I am also always using DAO code. When interfacing with sql server you have various options. From the mdb you can use DAO and ODBC or com ADO, and the coding efforts are about the same. In the ADP you also can use DAO with ODBC or ADO, but ADO is definitely easier to code against sql server tables. Anyway, that is my experience. My suggestion to the original poster is to experiment and read up on the various technologies available. Bottom line, there are always several ways to carry out the same processes. Which way is the best way? There really isn't any best way. It would be a matter of preference, or whatever is being done in your shop. For processing mdb tables from an Access mdb, it is easiest for me to whip up DAO code. For Sql Server tables from an Access ADP it is easiest for me to whip up some code with an ADO command object. I am happy that Microsoft added the Sql Server interface (adp) to Access. There is nothing easier for processing sql server tables outside of Query Analyzer than an Access ADP - quite a bit easier than a VS.Net project if you don't need multi-threading or delegates, etc. Plus, the ADP has some real nice features like the ServerFilter property, and the UniqueTable property which acts similar to the "Instead Of" trigger in sql server where you can manipulate a table that is joined to another table (which would otherwise be nonUpdatable in an MDB). There are just so many options that after you understand the technology it is a matter of preference. Rich Quick note: I have several Enterprise Access projects (.adp) that use a combination of VBA and ADO for interfacing with Sql Server. You cannot use DAO code in an ADP. You can only read data from sql server tables in an Access ADP with ADO - com ADO. com ADO can also be used with MDBs, but DAO is easier for reading data from Access mdb tables. Although, I use com ADO in mdbs for reading/writing data to Excel files. Plus, from Excel 2000 and forward, there is support for com ADO which is real nice for using properties/ methods within Excel for reading data from an Access mdb tables like Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=;Data Source=" & strPath cmd.CommandType = adCmdText cmd.CommandText = "Select * From tbl1" Set RS = cmd.Execute Range("A1").CopyFromRecordset RS '--also works great with Sql Server tables - just different connection string For .Net project (VB, C#) you use ADO.Net. The big difference between ADO.Net and com ADO is that ADO.Net is object oriented and you can do stuff like this (for sql Server): Imports System.Data.SqlClient (VB.Net) Use System.Data.SqlClient (C#) .. Dim cmd = New SqlCommand("Select * From tbl1, conn) or for Access it would be Imports System.Data.OleDb (VB) Use System.Data.OleDb (C#) .. Dim cmd = New OleDBCommand("Select * From tbl1, conn) And a quick plug for VS.Net - VB still uses a lot of classic VB syntax, but a lot of statements are interchangeable with C#. The big difference between C# and VB.net is that C# uses a lot of Brackets{} and semicolons. Otherwise, they have the same amount of functionality, both use delegates, interfaces, and the rest of OOP stuff. C# is just a little bit lower level - a little more granular than VB.Net (you can fine tune a C# app a little more than VB.Net). Just remember, that the thing with VB/VB.Net is RAPID APPLICATION DEVELOPMENT. There is nothing faster than any form of VB for rapdid application development - asp/aspx, exe, dll projects, interfacing with Sql Server/Oracle/Mainframes... There is no more ADODB in ADO.Net Rich I agree with Rich. When I was starting out, I used ADO for mdbs because Microsoft said that ADO was "newer and better and recommended", but then I decided (with help from this group and reading) that DAO is the better choice if you're sure your project is going to be an mdb and stay an mdb (or mde). > Which one should I use for Access Databases 2000 and up? Which one is > newer, better and recommended? Per Karen Hill: >Which one should I use for Access Databases 2000 and up? Which one is >newer, better and recommended? If your back end is .mdb, DAO gives more functionality and takes less coding. If your back end is C/S, ADO gives you more functionality - like pipelining multiple result sets down a single connection/hit. For my money, you should use DAO for going against .MDBs and ADO for C/S. DAO links will work for C/S - and maybe they're the right thing to do under some circumstances.... but I'd still favor ADO and stored procedures. SignaturePeteCresswell > Which one should I use for Access Databases 2000 and up? Which > one is newer, better and recommended? ADO was created later, which makes it newer, but Microsoft abandoned development of ADO in favor of ADO.NET, which shares little with ADO other than the name. Newness never means a damned thing. It was obvious to me when A2K was released that there was no point in using ADO against Jet data, even though that was what MS was pushing very hard. And now MS implicitly admits it was an error on their part, since now DAO is recommended by them for Jet data. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Which one should I use for Access Databases 2000 and up? Which one is > newer, better and recommended? I use ADO for everything. ADO has not been abandoned by MS. ADO is much simpler to code than DAO. ADO gives you many times the functionality than DAO gives you for both MDBs and ADOs. BTW, I have used DAOs extensively and expertly in the past. SignatureLyle Fairfield > Which one should I use for Access Databases > 2000 and up? Which one is newer, better and > recommended? You can use DAO with both Jet and with any ODBC-compliant server database. In fact, after a lot of cluttering their Knowledge Base, newsgroups, etc. with recommendations for ADP and ADO, the Access developers at Microsoft are recommending Access MDB, Jet, ODBC, and server DB over ADP, ADODB, and server. If you want "newer", that would be ADO.NET (which is not supported by any released version of Access). If you want "better", that would depend on who you ask (but see above), if you want "recommended", that would again depend on who you ask and when you asked them. The "classic ADO" that is available in Access is a dead end, superceded by ADO.NET which is not even built on the same object model. > The "classic ADO" that is available in Access is a dead end, superseded by > ADO.NET which is not even built on the same object model. On http://msdn.microsoft.com/data/mdac/default.aspx?pull=/library/en-us/dnmdac/html /data_mdacroadmap.asp MS says: "ADO: ActiveX Data Objects (ADO) provides a high-level programming model that will continue to be enhanced. Although a little less performant than coding to OLE DB or ODBC directly, ADO is straightforward to learn and use, and can be used from script languages such as Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®." CONTINUE TO BE ENHANCED Yes, didn't I mention "cluttering the knowledge base and their website with recommedations of ADP and ADO"? I don't doubt that, having done so, they will be forced to maintain ADO for some period of time, but I don't believe the promise of enhancements is really likely to happen. Check the Office 12 blogs for comments on ADPs and DAPs. Their URLs have been posted here. Larry Linson Microsoft Access MVP Larry Linson wrote: > The "classic ADO" that is available in Access is a dead end, superseded by > ADO.NET which is not even built on the same object model. On http://msdn.microsoft.com/data/mdac/default.aspx?pull=/library/en-us/dnmdac/html /data_mdacroadmap.asp MS says: "ADO: ActiveX Data Objects (ADO) provides a high-level programming model that will continue to be enhanced. Although a little less performant than coding to OLE DB or ODBC directly, ADO is straightforward to learn and use, and can be used from script languages such as Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®." CONTINUE TO BE ENHANCED > You can use DAO with both Jet and with any ODBC-compliant server database. > In fact, after a lot of cluttering their Knowledge Base, newsgroups, etc. > with recommendations for ADP and ADO, the Access developers at Microsoft are > recommending Access MDB, Jet, ODBC, and server DB over ADP, ADODB, and > server. They need to get the word out to somebody besides you because all the experts that get paid to rearrange the help files into $50 books don't know it yet. The last one I was browsing at Barnes and Nobles (something with Access 2003 VBA in the title) never mentioned DAO or ODBC. In fact the "Real World Application" presented in the manditory "Real World Application" chapter used ADO exclusively via recordset connection properties with no linked tables or bound forms anywhere in sight. >> You can use DAO with both Jet and with any ODBC-compliant server >> database. In fact, after a lot of cluttering their Knowledge [quoted text clipped - 10 lines] > ADO exclusively via recordset connection properties with no linked > tables or bound forms anywhere in sight. Then that book was written by a complete idiot who really had absolutely no understanding of the issues involved and had no business writing a book on Access. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >>>You can use DAO with both Jet and with any ODBC-compliant server >>>database. In fact, after a lot of cluttering their Knowledge [quoted text clipped - 14 lines] > absolutely no understanding of the issues involved and had no > business writing a book on Access. I don't know about complete idiot, but they certainly side stepped nearly every benefit of using Access in the first place. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.