ADO and DAO

[Evagrius Ponticus]

Hi all,

I am going through an Access course and it covers ADO and DAO. I am not sure which one to focus on; is there a disadvantage to learning one over the other. Thank you for any adivce.

 

[boblarson]

DAO is NOT DEAD!!!

As far as MS are concerned (currently anyway) DAO is obsolete and there are, to the best of my knowledge, no plans to make it 64 bit compatible. Whether, or when, that may affect you, I couldn't say, but I'd focus on ADO.

Um, that is utterly and completely erroneous. DAO IS being supported AND has actually made a comeback over ADO. Access 2007 was the first DAO that the Microsoft Access team has actually had the full control over as Microsoft has removed JET from the MDAC in Windows but it is being included with the Office installs with Access. It's name is

MIcrosoft Office 12 Access Database Engine Object Library (2007)
or
MIcrosoft Office 14 Access Database Engine Object Library (2010)

and it is very usable and I would say is the preferred method of working in Access because DAO is native whereas ADO is not. But, ADO still is a better choice most of the time when working with SQL Server from Access but as far as the rest of it goes, I would choose DAO.

 

[boblarson]

Thanks SydneyGeek! How do you recommend learning both without the two becoming mixed in your head . .?

I learned ADO first and for a long time it seemed easier to me because I could get by with just:

Dim rst As ADODB.Recordset
Dim strSQL As String
 
strSQL = "Select Field1, Field2, Field3 From MyTable"
 
Set rst = New ADODB.Recordset
 
rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

and then I was off and running. I always thought with DAO you had to use the workspaces and all of that. But have come to realize with the newer Access versions you can also simply use (depending on your need):

Dim rst As DAO.Recordset
Dim strSQL As String
 
strSQL = "Select Field1, Field2, Field3 From MyTable"
 
Set rst = CurrentDb.OpenRecordset(strSQL)

And I'm on my way.

So, anyway - how you remember is just by doing. And each person learns differently so I would maybe just create a cheatsheet to use for a while and pretty soon you should be good to go.

 

[xenou]

This article provides information on JET (including DAO) in the MDAC/Windows DAC:
http://msdn.microsoft.com/en-us/library/ms810810.aspx

@Bob:

DAO IS being supported AND has actually made a comeback over ADO. Access 2007 was the first DAO that the Microsoft Access team has actually had the full control over as Microsoft has removed JET from the MDAC in Windows but it is being included with the Office installs with Access. It's name is

MIcrosoft Office 12 Access Database Engine Object Library (2007)
or
MIcrosoft Office 14 Access Database Engine Object Library (2010)

Does this mean that the Access team can "keep DAO alive", or do whatever it wants with it? I see it being very useful as an Access-specific provider. But I could also see ADO being given enhancements that are specific to Access too so that they meet more in the middle (or rather that ADO becomes a more attractive choice with Access specific applications).

 

[boblarson]

This article provides information on JET (including DAO) in the MDAC/Windows DAC:
http://msdn.microsoft.com/en-us/library/ms810810.aspx

@Bob:

Does this mean that the Access team can "keep DAO alive", or do whatever it wants with it? I see it being very useful as an Access-specific provider. But I could also see ADO being given enhancements that are specific to Access too so that they meet more in the middle.

Yes, the Access has, and will, be continuing to keep DAO alive and enhance it as necessary and they are no longer constrained by having to work with another team to get things done with it. ADO may not get enhancements because Microsoft is moving away from ADO to ADO.NET instead and I don't know how that will affect the Office suite. But as long as we have classic ADO to work with, then at least we won't lose anything.

 

[xenou]

Do you know how ACE fits in with all of this? I have always thought of it as "JET for Access 2007". Is it really something different (I should of course already know more about this ...).

 

[boblarson]

Do you know how ACE fits in with all of this? I have always thought of it as "JET for Access 2007". Is it really something different (I should of course already know more about this ...).

As I wrote in my post ACE (Microsoft Office 12 Access Database Engine Object Library) is Jet/DAO. (14 for 2010)

 

[RoryA]

I stand corrected. I revise my initial statement to begin with "As far as most at MS are concerned".

I actually thought ACE was the OLEDB provider rather than being DAO - is that not the case?

 

[boblarson]

I actually thought ACE was the OLEDB provider rather than being DAO - is that not the case?

Nope, DAO.

It is basically the same Jet/DAO we've always had but with some enhancements because the Access Team could put those in since they now "owned" the product.

 

[Evagrius Ponticus]

Thank you for the additional information Bob!!

 

[xenou]

I actually thought ACE was the OLEDB provider rather than being DAO - is that not the case?

Funny I thought the same thing ...

Thank you for the additional information Bob!!

Second that.