Is it better to use ADO or DAO in Access 2007?

Asked 24/6, 2009 at 15:48 Answered 17/1, 2011 at 18:41

Solved vba ms-access ms-access-2007

When creating a new database in Access 2007, should ADO (ActiveX Data Objects) or DAO (Data Access Objects) be used?

Edit: Part of this database will be importing data from Excel 2007 spreadsheets.

Multifaceted answered 24/6, 2009 at 15:48 Comment(0)

[For the record, the official name for what once was 'Jet' is now the 'Access database engine'.]

For ACE (the Access2007 engine .accdb format) features it has to be ACEDAO.

For Jet 4.0 features it has to be ADO classic.

For Jet 3.51 features and earlier, pick either ADO or DAO. There are advantages and disadvantages to both. The vast majority of Access database engine functionality is common to both; the mutually exclusive functionality is arguable fringe. A lifestyle choice, perhaps, but no big deal. The smart coder uses the best of both :)

I've used both quite a bit and ADO is my personal preference. It is more modern than DAO, so architecturally it is an improvement: flatter object model, none of the tear down problems of DAO, etc. More properties and methods and introduces events (DAO has none) e.g. for asynchronous connection and fetching of records. ADO recordsets can be disconnected, hierarchical and fabricated, DAO recordsets cannot. Basically, they took the good things about DAO and made them better.

DAO is not without its strong points. For one, you will find more DAO code examples than ADO for Access/Jet.

P.S. for some reason, folk who like DAO really dislike ADO. Ignore the propaganda. ADO isn't deprecated. The ACE has an OLE DB provider and is currently the only way of using ACE in 64 bit. ADO.NET hasn't replaced ADO classic any more than VB.NET has replaced VBA6 in Access projects.

EDIT: just to clarify, "For Jet 4.0 features it has to be ADO classic," this is because DAO 3.6 only received a few enhancements for the features new to Jet 4.0. For example, for the DECIMAL data type you cannot specify scale/precision. Other features are missing completely from DAO. For example, can you do the following in Jet 4.0 using DAO (or ACEDAO in ACE for that matter)?

CREATE TABLE Test (
   col1 CHAR(4) WITH COMPRESSION DEFAULT '0000' NOT NULL, 
   CHECK (NOT EXISTS (
                      SELECT T1.col1 
                        FROM Test AS T1 
                        WHERE T1.col1 <> '0000' 
                        GROUP 
                           BY T1.col1 
                       HAVING COUNT(*) > 1
                      ))
);

(hint: compressible fixed-width text column with table-level data integrity constraint.) No, you cannot.

AFAIK the only enhancements to ACEDAO was for the new ACE functionality i.e. they didn't go back and fill in the Jet 4.0 gaps in DAO. And why should they? We still have ADO to plug the gaps. Better that the team spent their time more productively, like fixing that annoying DECIMAL sort bug, for me the best thing about ACE ;-)

Constipation answered 24/6, 2009 at 23:10 Comment(11)

"[For the record, the official name for what once was 'Jet' is now the 'Access database engine'.]" That is NOT TRUE. That is the name of the new database introduced in Access 2007. It does not change the name for all previous versions of Jet. And it's still JET, regardless of what they call it. – Anthozoan 25/6, 2009 at 1:4

No, the new engine introduced in Access 2007 is ACE. Access database engine is the collective term for Jet and ACE. I thought you were the one who likes to be precise...? – Constipation 25/6, 2009 at 7:24

This mistake you make is assuming that when someone says 'Access' and they mean the database engine you erroneously correct them as 'Jet' without considering that they may be using ACE. The way MS have addressed this issue is to coin the term 'Access database engine' to be ambiguous yet precise. – Constipation 25/6, 2009 at 7:46

"For Jet 4.0 features it has to be ADO classic." I disagree with singling out Jet 4.0 here. To me it's still a case of DAO vs ADO and no real reason to choose ADO when using Access as the FE and Access database as the backend. DAO has a lot more functionality in many areas. – Jobie 25/6, 2009 at 21:32

Jet 4.0 has features that DAO cannot reach. No harm in using both, no? – Constipation 25/6, 2009 at 23:29

"DAO has a lot more functionality in many areas" -- but as a whole ADO has more functionality than DAO. Take the recordset object from each and compare: the number of data types; the number of properties, methods and events; the architecture (disconnected, hierarchical, fabricated, etc). It's no contest... no really, you are free to use ADO and DAO, they are not in competition. – Constipation 25/6, 2009 at 23:35

The additional capabilities of ADO mostly don't apply to Jet data. About the only major thing that ADO provides is disconnected recordsets, and, frankly, I have never needed them. That seems to me to be something that's more useful with a server database where you want to do batch operations without maintaining a connection. That's pretty much irrelevant for Jet data. The only useful thing about disconnected recordsets is that you can create synthetic recordsets (i.e., not based on a table) and use them like a regular one. I haven't ever needed that myself, but it does sound intriguing. – Anthozoan 26/6, 2009 at 20:20

"The additional capabilities of ADO mostly don't apply to Jet data" -- funny but I can't think of a single one that doesn't apply to Jet data. For recordsets alone: paging, batch updates, asynchronous fetching of large resultsets, hierarchical (recordsets within recordsets), filtering using LIKE pattern, GetRows and GetString for output of array/formatted text, persisting on disk, output as XML, ... I could go on. All these work well with Jet data. Think about it: why wouldn't they? – Constipation 26/6, 2009 at 23:34

"synthetic recordsets... sound intriguing" -- yes, I've used them as a container object custom collection classes in VBA: use a Field for the index (could support multiple indexes using multiple Fields) making the indexes retrievable (unlike a VBA.Collection object), IUnknown data type, but the real advantage are the built in properties and methods: Sort, Filter, GetRows (array output), GetString (formatted text output) so you don't have to roll your own... – Constipation 26/6, 2009 at 23:39

...another is passing Parameter objects around to support strongly-typed NULLable arguments as an alternative to Variants. Though I suspect the question is intended to relate to usual data access functionality rather than these 'stand-alone' uses for ADO objects. – Constipation 26/6, 2009 at 23:43

Another good one is using the MsDataShape provider and SHAPE syntax to append columns that are updatable (on the client side, that is, can't be written back to the db), which works well with Jet e.g. here's one I did earlier on SO: #603339 – Constipation 26/6, 2009 at 23:49

DAO is the is the recommended technology here. ADO has much been depreciated, and is now being replaced with ADO.net.

Not only is DAO the native and recommended data object model for using MS access, it continues to be enhanced and now has a whole bunch of new features for sharepoint. In access 2007 we now have Support for SharePoint lists. This means that new DAO object model for 2007 allows a sharepoint list to be used and viewed as a SQL server table. That means you can use SQL on sharepoint lists (in fac there not even a oleDB provider that allows you to use SharePoint lists this way, but with DAO you can now do this). There is nothing of this sort that been added to ADO. So SharePoint lists from a access (dao) point of view sees these SharePoint lists as a standard table.

Furthermore DAO in access also has support for what we call complex data types. This was done to support XML lists from sharepoint. Keep in mind for the next version of access (2010) we are going to see a whole bunch more new additional features being added to DAO (JET is now called ACE).

So it is without question that DAO is the correct and good model to use. ADO is not receiving any more enhancements, and has been superseded by ADO.NET.

So the future belongs to DAO, and it’s clear that’s where Microsoft is investing its money in terms of MS access and terms of upgrading Access to work with things sharepoint.

Access 2007 received multi-value capabilities for its field definitions, and again this was a result of enhancements for supporting sharepoint. However, these features are part of JET and these enhancements can be used without sharepoint. they are now part of DAO.

edit: Perhaps I’m going to expand on this a little bit, and try to clarify what we have such opposing answers here, I can assure you that when using access 2007, you’re far better off to use DAO.

Where the confusion stems from, is if you look of the tools references when you choose to use the default data object model access 2007, the problem here is it’s not called DAO anymore. It is now called ACE.

When you use DAO in access 2007 You’ll note in the tool references, the reference is not set to DAO 3.6 ( that version has been depreciated, and also is now not part of MDAC download anymore). You’ll notice that the new reference when using DAO in ms-access is called:

Microsoft office 12.0 access database engine Object Library

Now the above is a bit of a mouth full, but the above is the correct for reference access 2007 when you’re going to use DAO in place of ADO.

In other words, perhaps we should call this DAO II.

In other words, this data engine continues to be enhanced, and will most assuredly see a 64 bit version of this engine for office 2010 (office 14).

So the question or confusion centers around what term were going to use when we refer to using DAO in access 2007. The confusion here is in fact that the documentation and even the tools ->reference does not call it DAO.

At the end of the day in access 2007, if you plan to use DAO then that means that you set the above reference, and do not set a reference to DAO 3.6. Regardless, it makes absolutely no sense to start using ADO now when it’s been depreciated, and the new DAO object model for access continues to be enhanced and invested in by Microsoft.

I hope this helps in clearing up the confusing here. While DAO/JET It’s being depreciated, the new version access 2007 is based on the same code base, except it continues to get enhanced. So the new data engine in access can be considered and called the new DAO object model.

I’m currently under NDA on this issue, but I can most surely tell you that for the next version of office (2010) we are going to see a whole slew of enhancements again.

So it is near unanimous among Access developers that when developing access applications and using the native data engine, the preference here is to use the DAO object model ( but keep in mind we’re not calling it that anymore, we called it ACE).

Rh answered 24/6, 2009 at 16:22 Comment(18)

"ADO has much been depreciated" -- hmm, I must have missed that announcement from Microsoft. I don't suppose you have a citation, link, etc? – Constipation 24/6, 2009 at 22:34

"[ADO classic] is now being replaced with ADO.net... has been superseded by ADO.NET" -- let's not go off topic. How is it possible to use ADO.NET in Access2007? – Constipation 24/6, 2009 at 22:37

DAO in its ACE guise has still not been enhanced for the Jet 4.0 feature set and cannot run in ANSI-92 Query Mode, hence is useless as regards creating CHECK constraints, fixed length or compressible text columns, the BINARY and DECIMAL data types, row-level locking, etc. ADO always runs in ANSI-92 Mode, therefore is still required to programmically create these objects via SQL DDL. – Constipation 24/6, 2009 at 22:41

"this data engine continues to be enhanced" -- again off topic but I can't resist... How does the removal of major features such as user level security and replication amount to a net 'enhancement'? Surely I'm not the only one decidedly unimpressed by SharePoint integration and a new way to violate 1FN with non-scalar types (I know you are a long-time multi-value advocate but, unlike PICK, ACE hasn't been given expressions to handle multi-value types, has it now?) – Constipation 24/6, 2009 at 22:46

I keep telling you this but you don't seem to get it: Access developers as a rule don't give a rat's about DDL -- they don't use it, they don't need it. The fact that ADO supports some Jet DDL that DAO does not is an artifact of MS's internal power struggles and has zero effect on Access developers. – Anthozoan 25/6, 2009 at 1:11

How do you create a CHECK constraint without SQL DDL? I could show you some common SQL 'design patterns' that Access developers use all the time that require CHECK constraints to implement fundamental data integrity e.g. a sequenced primary key on a valid-state temporal table. "don't give a rat's" a.k.a. "Ignorance is bliss" ;-) – Constipation 25/6, 2009 at 7:28

A few things. The fact of having more or less ansi-92 compatibility is NOT a argument that says features are being removed or added here. Perhaps you want some features in regards to sql-92 and that is just fine. It would be nice for more features in this regards but it changes NOTHING in regards to new features being added to JET here. Furthermore the ACE engine DOES support user level security when you use the mdb file format. So the feature not been removed. Furthermore just because a feature been removed does not negate the fact that other new features are being added. – Rh 25/6, 2009 at 15:52

Again why you are you making claims of no new features when clearly there are? Why make statements here that just serve to confuse people here? What exactly is your goal here by doing this? As for the query engine supporting complex data types, yes it has been changed. While you don’t care about SharePoint, the REAL issue here is the use of XML. Perhaps you never heard of XML or don’t care about it either or don’t consider it important to our industry. Some of us do. And yes the query engine in access has been changed to support complex data types (multi-value fields). – Rh 25/6, 2009 at 15:53

Select FirstName, LastName, FavorateColor where FavorateColor.Value = “blue” and FavorateColor.Value = “green” The above will actually return customers that have both favorite colors. In normal sql, the above (assuming that the favorite colors is a child table) would take both a join statement, and also likely a group + count get the correct result. If you don’t care for SharePoint stuff then it is no big deal. New features and enhancements in this regards are new features and enhancements. Because new features are not ones you care about does not warrant an unfair point of view. – Rh 25/6, 2009 at 15:54

There is new features and enhancements occurring for the new JET engine. Why try to spread misinformation in this regards? This is just so unprofessional on your part. I just don’t understand what your final goal here is? – Rh 25/6, 2009 at 15:55

"I just don’t understand what your final goal here is?" -- you were around in the Jet 4.0 era, right? Do you remember all those Access users begging for SharePoint integration? Recall those user group where customers were banging on the table demanding multi-value types? No, me neither. My point: the features ACE delivered weren't those on users wish lists. I don't believe I'm the only one who was underwhelmed by ACE engine-level features. – Constipation 26/6, 2009 at 0:13

"the REAL issue here is the use of XML" -- then why no XML data type and XQuery for ACE, a la SQL Server 2005? Actually, don't answer that. While it's true I'm having fun, we've gone far from the question here. I suggest you ask your own question on SO about ACE and users' expectations. I'm sure I'd be happy to contribute an answer. – Constipation 26/6, 2009 at 0:20

Lack of xQuery does not mean features were not added for xml (how stupid is that?). I said new features for ACE are to work with SharePoint. SharePoint lists are based on XML. The new complex data type and the attachment fields in ACE 2007 follow the same structure and naming conventions as SharePoint lists. FACT IS features are being added to JET/DAO. ADO is not seeing any new features and is thus on the back burner. ADO been superseded by ADO.NET. Depreciation might not be the correct term, but no new ADO features is not good and fact is DAO for access 2007 is receiving new features. – Rh 29/6, 2009 at 15:27

You previously said, "the REAL issue here is the use of XML" but now you seem to be agreeing with me that the REAL issue here is SharePoint. A new feature yes but with limited application. Your example where FavorateColor.Value = “blue” and FavorateColor.Value = “green” just looks like a bug to a SQL coder. As I said, multi-value types deserves dedicated syntax a la pick but they are absent. It be interested to learn whether you are using multi-value data in non-SharePoint Access projects. – Constipation 2/7, 2009 at 12:15

When the Access team takes the time to implement all the Jet 4.0 functionality into ACEDAO the ADO will have been superseded for the Access data engine (does ACEDAO support row level locking yet?) When the Access team takes the time to implement all the ADO objects' functionality (disconnected asynchronous-fetching hierarchical recordsets and all that Jazz) then ADO will have been superseded by ACEDAO. When the (??) team makes ADO.NET work in VBA then ADO.NET will have superseded ADO. Until then ADO is still needed full to provide full functionality in the Access world. – Constipation 2/7, 2009 at 12:22

Pick never had different query syntax when using MV or regular columns. MV features are result of SharePoint lists but lists are XML. I field Many questions from people using MV features without SharePoint. Popular use is the new cool control allows people to select multiple values with check boxes on a form (no codeor junction table needed). The new attachment field in access is also multi-valued and that allows users without coding experience to attach many files to ONE record. So MV features are now OFTEN used without SharePoint. Access 2010 will also have additional new features for ACE. – Rh 2/7, 2009 at 21:11

I don't know where you are fielding these questions but if SO is anything to go by user level security question are more popular Access2007 MV data types ones (haven't seen single one yet myself). But let's not be distracted from the question. If someone is using MV then indeed it has to be ACEDAO. If they are using Jet 4.0 features missing from DAO (I saw a SO question last week using Jet 4.0 compressible text data types) then it has to be ADO. – Constipation 3/7, 2009 at 8:54

I can add here that using DAO from .NET, via the Primary Interop Assembly, works very well. Even from .NET I prefer to use DAO rather than ADO.NET when talking to Access databases. – Ondrea 31/1, 2012 at 19:45

The answer to the question depends on what you're doing. If you're using Access to work with data that's in a format whose ADO interface is more versatile, then use ADO. If you're using Jet data, or using the Jet database engine to work with another database engine (via ODBC), then DAO is the right choice.

But that answer assumes you're working from Access. If you're working from some other programming environment, the answers will likely be completely different.

Anthozoan answered 25/6, 2009 at 1:7 Comment(0)

ADO is the current recommended access method. I think DAO has been deprecated for quite a number of years.

Looks like its been since Access 2000 - according to this link,

List of obsolete data access technologies - http://msdn.microsoft.com/en-us/library/ms810810.aspx#mdac technologies road map old_topic9

Quote from the above article, which was revised Dec 2008 - "Data Access Objects (DAO): DAO provides access to JET (Access) databases. This API can be used from Microsoft Visual Basic, Microsoft Visual C++, and scripting languages. It was included with Microsoft Office 2000 and Office XP. DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system."

Cataplasia answered 24/6, 2009 at 15:54 Comment(15)

I'm trying to pull in data from an external source into an Access DB, and I've been finding conflicting information, so thanks for sorting it out. Also, MS really needs to fix the MSDN search since its easier to Google site:msdn.microsoft.com than "Bing" it. – Multifaceted 24/6, 2009 at 16:4

I use DAO from VBA within my Access databases, because Access itself is still using DAO last I checked, and use ADO for accessing Access databases from outside Access. – Ioannina 24/6, 2009 at 16:48

KB 225962 only applies to Access 2000 and Access 2002. In Acess 2003 the default was DAO and no ADO. Thus that KB article is rather obsolete. – Jobie 24/6, 2009 at 23:46

@David: "Access itself is still using DAO last I checked" -- I occasionally hear this piece of folk law but no one can ever provide a citation, reference, etc? I don't suppose you are any different...? – Constipation 25/6, 2009 at 7:30

@onedwywhen. I was wrong in my last comment in this subthread with respect to ADO not being in A2003 by default. Create a new MDB in Access 2003 or ACCDB in Access 2007. Ctrl+g to open the VBA editor. Tools >> References. In A2003 You will see DAO in the list of references before ADO. In A2007 you will see the ACE reference which is the updated DAO reference. – Jobie 25/6, 2009 at 21:27

The article "List of obsolete data access technologies" was not written with Access in mind. And should be disregarded in an pure Access environment, that is where Access is both the application part and the data store part. – Jobie 25/6, 2009 at 21:34

I don't think folk should read too much into which references are checked by default. You are free to set references to ADO and DAO, it only takes a few seconds ;-) – Constipation 26/6, 2009 at 0:27

The out-of-the-box defaults do matter in Access. When MS was pushing ADO, they defaulted to ADO and left out DAO. This was wrong for most Access apps, which use only Jet data. They realized they'd made a mistake and a couple versions later, they defaulted back to DAO, as they should have all along. It does matter, partly because there are overlaps between the object models (two incompatible recordset objects, for instance). The ADO default was hostile to the true nature of Access and had nothing to do with the interests of Access developers. That attitude has changed, thankfully. – Anthozoan 26/6, 2009 at 20:15

In regard to the 2008 revision date on the KB article: those dates are completely meaningless. They get changed all the time without updating content that has existed in the same form for over a decade. You'll find articles for Access 2 that have recent update dates, yet don't reflect any actual new content. – Anthozoan 26/6, 2009 at 20:17

@Ioannina W. Fenton: "It does matter, partly because there are overlaps between the object models" -- those overlaps are easy to deal with. Google around and look at some actual code. DAO code largely omits the 'DAO.' library qualifier and ADO code almost exclusively uses ADODB or ADOR qualifiers, which resolves the problem. You'd have to be particularly clueless not to get ADO and DAO code working in the same project... and you are not that clueless, so you are letting past politics affect your better judgment, methinks. – Constipation 27/6, 2009 at 13:29

@Tony Toews: does Access2007 out-of-the-box have references set to both ADO and DAO? (I don't have it to hand right now.) I assume it must for the CurrentProject and CurrentDB objects respectively. – Constipation 27/6, 2009 at 13:33

(I didn't see this when it was new, so I'm responding now that I see it) @onedaywhen: you may not think there's any problem with resolving the ambiguities, but it's one of the most frequently-asked questions, with people having recordset problems, because they don't know to qualify their variable definitions. When A2000 came out, even though I had no intention to ever set a reference to ADO in any Access app I ever created, I started qualifying all my DAO variables, just in case somebody in the future added an ADO reference. Every competent Access developer I know of does that. – Anthozoan 17/1, 2011 at 23:40

CurrentProject and CurrentDB don't have any dependence on the references because they are members of the top-level Application object, and can be used even without either ADO or DAO references. You then have to type your variables as objects or variants, but you can still use the objects without references to their corresponding data interface library. So, in a standard newly-created Access app (A2003 or later), the DAO reference is set, and the ADO reference is not, but CurrentProject is still usable (and a good way to use the tiny number of things ADO is useful for). – Anthozoan 17/1, 2011 at 23:44

@David-W-Fenton: see #4657135. – Constipation 18/1, 2011 at 11:30

@onedaywhen: Why are you posting that link here? It does not seem to have anything at all to do with this question or answer. – Anthozoan 18/1, 2011 at 23:48

It depends on your needs. Neither tool is expected to disappear soon.

If you don't have experience in either ADO or DAO, you'll find DAO is much, much easier. So unless you need ADO, use DAO.

You added this critical item: "I'm trying to pull in data from an external source into an Access DB." This connectivity may require ADO.

Guerra answered 24/6, 2009 at 17:18 Comment(3)

I'm pulling in from Microsoft Excel, if that has any impact. – Multifaceted 24/6, 2009 at 17:23

Then I'd use DAO. My experience in ADO was forced solely by the need to link with non-Microsoft databases. – Guerra 24/6, 2009 at 21:7

Clarification: I meant to say by the need to link with non-Microsoft programs, specifically MySQL. For anything in MS Office, DAO is primed to produce. – Guerra 25/6, 2009 at 4:27

DAO just rocks in terms of performance compared to ADO. There's no comparison.

Loraineloralee answered 29/10, 2009 at 19:37 Comment(2)

"DAO... compared to ADO. There's no comparison." -- make up your mind, LOL! – Constipation 17/1, 2011 at 19:59

Yeah, I should have read that a bit closer before posting. But nonetheless, DAO is far faster than ADO. – Loraineloralee 18/1, 2011 at 13:30

Apologies that this is an answer, when it should have been a comment (I do not have the rep), but I wanted to clear up an erroneous claim that DAO/ACEDAO does not support Jet 4.0 record-locking. It does, and that is the default behaviour, irrespective of what certain MS articles claim.

The problem is this may introduce huge bloat (hugely fragmented DB file) when using DAO edit/update and you cannot turn it off in DAO/ACEDAO.

If you do have this issue, you can turn it off via first opening the database via an OLEDB connection using the correct Jet OLEDB:Database Locking Mode settings, which will allow you to set the database to page-level locking. This property will then be respected by consequent connections, DAO or otherwise, so you can then use DAO for fast updates etc.

This will then allow DAO to revert to the usual 8X performance compared to executing SQL statements.

Here are a couple of links pointing to the issue:

Does ACEDAO support row level locking?

http://www.access-programmers.co.uk/forums/showthread.php?t=47040

MS KB article, including code of setting locking mode with ADO, then using DAO on that DB - http://support.microsoft.com/?id=306435

Equiponderance answered 17/1, 2011 at 18:41 Comment(1)

I have not see anyone post code anywhere that shows how to open a DAO recordset and specify record vs. page locking. I don't believe it's possible, as there are no options defined to allow one to specify that. If you can find such code, please post it. – Anthozoan 18/1, 2011 at 0:9

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags