What does DAO have that ADO/ADOx/JRO do not?

An interesting and still very relevant seven year old article by Michael Kaplan aka michka. What does

I noticed on my two month old Vista Machine, that I only have one referrence for DAO - the rock solid 3.6,  but for ADO I have:

2.0

2.1

2.5

2.6

2.7

6.0

Oy.

• "Running transactions that use multiple databases"

I admit without grudge that this sounds like a nice feature (though I'd consider .NET transaction services before porting my apps back to Jet <g>). Do you get the impression ADO was create to solve other problems...?

--

• "Opening a table in a mode that keeps others from opening it read-write mode"

Believe me, using ADO+Jet to create a table lock isn't too hard to achieve (indeed, *avoiding* a table lock often is <g>!)

--

• "Creating users and groups in a way that allows you to recreate them in case an MDW file is lost …works in DAO using CreateUser/CreateGroup which allow you to specify PIDs"

Again, ADO has a *different* way of achieving the same end result i.e. by using SQL code rather than working with an object model in COM code. Works in ADO when you use SQL DCL e.g.

CurrentProject.Connection.Execute "CREATE GROUP Billing Gu294JxP1m, Shipping Kl27c5sI9h;" CurrentProject.Connection.Execute "CREATE USER Tim pwd H3sJaZ9k2m;"

where the alphanumeric strings are the PIDs. For further details see:

Advanced Microsoft Jet SQL for Access 2000

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

--

• "Securing Access project objects such as forms, reports, or macros."

Generally, Jet's OLE DB providers only works with Jet database objects _by design_; DAO does not expose certain Jet objects _by omission_ such as CHECK constraints, Jet 4.0-specific data types, or default parameter values ("CREATE PROCEDURE MyProc (my_param CHAR(11) = '{{DEFAULT}}') AS ..."). However, in this particular case, security for forms, reports, and macros are exposed to ADO via SQL DCL e.g.

CurrentProject.Connection.Execute "GRANT DELETE ON OBJECT MyReport TO PUBLIC;"

Again, see Advanced Microsoft Jet SQL for Access 2000 (link above).

--

• "Ability to create a linked ODBC table that is updateable"

I have no comment because I have no need to do this.

--

• "Ability to create "Prevent Deletes" replicas"

This is no longer relevant because replication has been removed from Jet (as of Access 2007).

--

• "Method for determining folder information from Exchange/Outlook folders and columns"

You expect a data access technology to do this <g>?! n

--

•" Method for determining folder information from Exchange/Outlook folders and columns"

Not sure what you are getting at here. You can certainly use ADO to get a list of Exchange folders and query on folder properties e.g.

SELECT "DAV:displayname", "DAV:contentclass", "DAV:href"

   FROM SCOPE('shallow traversal of

       "http://gomezawin2000.gomezamobile.local/public"')

   WHERE "DAV:ishidden" = False

   AND "DAV:isfolder" = True

See:

Accessing the Web Storage System Using ADO and ExOLEDB

http://www.informit.com/articles/article.aspx?p=167787&seqNum=10

--

• "Capability to set and change Jet options without making registry changes ...works in DAO through ... DBEngine.SetOption"

First, you can set many, many Jet options without making registry changes. Second, the options specific to DBEngine.SetOption can indeed be set using ADO with the Jet OLE DB providers. See:

ADO Provider Properties and Settings

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp

Table 5. Provider-specific ADO Connection Object Session properties

Jet OLEDB:Recycle Long-Valued Pages (read/write), Jet OLEDB:Page Timeout (read/write), Jet OLEDB:Shared Async Delay (read/write), etc.

Third, there are OLE DB provider settings unavailable to DAO e.g. Jet OLEDB:Database Locking Mode to set to Row-level Locking; see:

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60

http://support.microsoft.com/kb/306435/en-us

--

• "Allowing the creation/change/deletion of any and all properties through the JPM -- also known as the Jet Property Manager"

I've never heard of the "Jet Property Manager" and it seems I'm not the only one: 2 hits on Microsoft.com and four distinct English language hits on Google (not counting this page!)

--

• "Forcing the locking mode of a database when working from within Access (works in DAO through the DAO.LockTypeEnum constants while using CurrentDb, fails through the ADO.LockTypeEnum constants while using CurrentProject.Connection)"

Agreed, I don't think this is possible (though personally I can't see the need). AFAIK only ADO recordsets have a LockType property and CurrentProject.Connection returns an ADO Connection object.

--

• "Retrieving implicit permissions on an object ...works in DAO through the AllPermissions properties"

Once again, SQL DCL provides keywords ALL PRIVILEGES and PUBLIC respectively.

--

• "Allowing a separate Jet session to run using a special object in the object model"

ADO allows a separate Jet session to run e.g. by creating a new ADO Connection object.

--

Now, how about a list of what does ADO have that DAO does not? Well, sorry, I don't have the time but asynchronously fetching a hierarchical recordset then disconnecting it and saving it to disk as XML springs to mind :)

Gosh, I've just had an amazing idea: what if we could use both ADO *and* DAO...?

Jamie.

--

You might add to your list the fact that accessing rows randomly in an indexed table using the recordset.seek method does not work in ADO.  This can become a significant perfromance problem in large tables because every alternative to .seek requires using a query.  If your database is designed well, .seek works instantaneously.

Jamie Collins might write back and point out that .seek does not work with linked tables under DAO.  This is really not a problem if you know how to use a workspace(s) to access tables in another database.  You can still explicitly link to the same tables if you need to allow your users to create and run their own queries.

Quote - "However, in this particular case, security for forms, reports, and macros are exposed to ADO via SQL DCL e.g.

CurrentProject.Connection.Execute "GRANT DELETE ON OBJECT MyReport TO PUBLIC;" "

Except it doesn't work on Forms.  Everyone says it works, but it doesn't.  Go try it.

...and it's back to fiddling with containers in DAO, urgh.

>> CurrentProject.Connection.Execute "GRANT DELETE ON OBJECT MyReport TO PUBLIC;" "

Except it doesn't work on Forms.  Everyone says it works, but it doesn't.  Go try it. <<

No thanks, though it sounds like a bug with Jet, not ADO ;-)

Jamie.

--

>> Jamie Collins might write back and point out that .seek does not work with linked tables under DAO. <<

Jamie Collins actually writes back to tell you that I don't use .seek in ADO (or linked tables at all, for that matter). Rather, I use either .Filter or a database round trip depending on circumstances. I think expecting to port your DAO code to ADO code "as is"  is not a good approach; rather I think you should look at the advantages ADO can (or cannot) offer, rather than try to find things that DAO can do that ADO cannot (FWIW I'm convinced that the list of things ADO can do that DAO cannot is the longer one).

Jamie.

--

>> Actually, it is a bug with the way Jet maps the syntax to Access containers -- but one can hardly claim a functionality works well if it is broken, no matter who one wishes to blame (especially when good old DAO continues to work just fine here).... <<

My point is that many things in work just fine with Jet in ADO even if there is a slightly (or otherwise) different way of achieving the same thing or something very similar (or perhaps even better).

ADO has headline features that improve upon DAO e.g. (pulls one out of the air) asynchronous fetching of recordsets. This is to be expected because ADO capitalized on the success of DAO, improved upon some of its weaknesses and introduced some new functionality.

If you expected ADO and the Jet OLE DB providers to re-implement 100% of ADO+Jet functionality then I think your expectations were set unreasonably high. The nitpicking nature of your article (takes one to know one <g>) indicates that MS did a pretty good job on the major functionality that most people care about.

The aim of my comments was to refute you claim that your article "is still entirely true" and I think I did a pretty good job ;-)

I don't think you can have taken a good look at ACE because it is a *big* disappointment: as regards headline features they have added muli-value fields (a.k.a. First Normal Form violation for newbies) and removed user level security and replication. DAO for ACE still doesn't have support for the new features of Jet 4.0 (e.g. CHECK constraints, compressible data types, row level locking, etc) and ADO is still requires to plug these gaps in DAO.

I'll promote the use of *both* ADO and DAO, even though I have a strong personal preference for ADO (because of the simplicity of its object model and richer feature set) until they have 'fixed' DAO. I invite you to join me :)

Jamie.

--

I think we're done here, although in parting I would say it still seems to me there are some great reasons to use *both* ADO and DAO selectively and your comments seem to reinforce this point. One thing that puzzles me though:

>> But core JPM support? <<

Why are people not discussing JPM (whatever it is) in the newsgroups? Why are there so few hits on google and MSDN for JPM? If there is a relevance I'm not seeing it.

Jamie.

--

Erm, didn't you just tell me what its called <g> ?! I have tried searching for "JMP" and "Jet Property Manager" and can turn up only a few web pages. I therefore conclude it is something of limited appeal (your "except known ones" comment suggests it could even be an undocumented/unsupported feature), therefore underlining my point that the things that DAO can do that ADO cannot are of limited interest to the general Access punter. Contrast with CHECK constraints, one of the more obscure and underappreciated features of Jet 4.0 (and not "available" to DAO), which I can turn up at least 400 hits in the Access newsgroups searching by the exact string "CHECK constraints".

Jamie.

--