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

by

[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 😉

More Related Contents:

  1. ms access vba code for update records through form
  2. How to loop through all controls in a form, including controls in a subform – Access 2007
  3. SendKeys is messing with my NumLock key via VBA code in Access form
  4. How to add default signature in Outlook
  5. Combine rows / concatenate rows
  6. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  7. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  8. Too few parameters Expected 1, recordset issue
  9. Show “Open File” Dialog
  10. How do I display a messagebox with unicode characters in VBA?
  11. Determining whether an object is a member of a collection in VBA
  12. Distinction between using .text and .value in VBA Access
  13. MS Access VBA Error: Run time error ’70’ Permission Denied
  14. Automatically generating handling of issues
  15. VBA – Folder Picker – set where to start [duplicate]
  16. MS Access: how to compact current database in VBA
  17. FileDialog doesn’t work
  18. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  19. Determine real cause of ODBC failure (error 3146) with ms-access?
  20. Is there an equivalent to Thread.Sleep() in VBA
  21. How to refer to Excel objects in Access VBA?
  22. Exporting MS Access Forms and Class / Modules Recursively to text files?
  23. How to get logged-in user’s name in Access vba? [duplicate]
  24. Pass a parameter into an access report programmatically
  25. finding latest file in a folder and opening it (vba access)
  26. Insert multiple records with a date range in MS Access
  27. MS Access VBA: Sending an email through Outlook [closed]
  28. Runtime error 287. Sending Emails through Outlook using VBA in Access
  29. Trouble with InputBoxes
  30. Saving a Excel File into .txt format without quotes

Leave a Comment