Discussion: Pros and Cons of DAO v ADODB

[patjones]

I wasn't sure where to discuss this. For now I'm content to bring the issue up with you experts and see what happens.

I had a long phone conversation this afternoon with a potential employer, for an Access/VBA developer position. He made a claim that ADO is far superior to DAO, and in fact to hear him talk about it you might think that DAO is a deprecated feature. He claimed that Microsoft will do away with DAO in future releases of Access/VBA.

I've tried to look into this matter, with wildly varying results ranging from what this gentleman told me, to people saying just the opposite...that DAO is the preferred data access method. Most of my projects utilize DAO, because I find that whenever I use ADO the amount of coding required seems to be greater.

I'm not looking for sympathy toward DAO so that I can keep all my projects as they are, rather just an objective assessment of what the best practice is. If you guys want me to open a thread up in the questions section, then let me know. Thanks.

Pat

[NeoPa]

This should be an interesting discussion Pat, and I've heard talk of it myself recently too. Here's my understanding of what happened and where it's headed.

Originally, DAO was designed and used for Access or Jet specific objects. It was limited in that it didn't handle outside tables or recordsets too well. ADODB, on the other hand, being more general purpose, did. ADODB however, didn't have quite the bells and whistles that we expect from using Access, so DAO was used generally internally to Access/Jet.

Then someone in the Access team (or maybe elsewhere - I don't know) had the idea to standardise things towards ADODB across the board (by which I mean for Access/Jet and outside tables). Later versions of the product had their recordsets and the default library built on ADODB.

Later still, they realised that actually, ADODB does a fine job of handling foreign/outside recordsets, but can't quite match what people are used to using DAO, hence 2010 (I think - It may even have been 2007, but I think 2010) reverted back to using DAO as standard and default. We are even starting to see a few questions where projects fail in 2010 that were designed in 2007 because the ADODB reference, which the project was built to use, is no longer available by default in 2010.

So, the good news for those of us that still make use of DAO to support all the features we're used to having available in Access, is that those people who were pushing for ADODB as a library for all seem to have learnt that it was never quite as simple as that. But remember DAO doesn't do well with foreign/external data sources. ADODB is still the choice for those I would say.

[sierra7]

NeoPa,
Last para, first line I think you mean DAO not ADO, otherwise i agree with everything. The move to ADO was 2000 & 2002 but back to DAO in 2003.

This is discussed at length by Tom Wickerath an Access MVP

http://www.accessmvp.com/TWickerath/articles/adodao.htm

Obviously both can coexist in the same project but must be referenced explicitly. In the case of Access 2010 you need to check there is a referent to c:\Program Files\Common Files\System\AD O\msado25.tlb. I just wonder what msado25,27 & 28 do?
Personally, I have never tested ADO as all my backend SQL Server stuff uses ODBC. I'm just starting to look at web-developments and will need ADO for web forms (I believe)
S7

[patjones]

Perhaps it's a question of my never having needed functionality in one that is not available in the other. I mean, if you're doing something like connecting to the data source and populating and enumerating through a recordset on the client side - I can't see an advantage in one over the other.

Let's make it concrete and take a simple example. I wrote some ADO to do a text box search for a grant number (PIN) and pull the grant name and expiration date and display them on the form:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim paramPIN As New ADODB.Parameter
Dim rst As ADODB.Recordset
Dim var As Variant

'Define and open connection
 cnn.ConnectionString = "PROVIDER=SQLOLEDB; DRIVER=SQL Server; SERVER=XXX; DATABASE=XXX; UID=XXX; PWD=XXX"
 cnn.Open

'Define ADO command
 cmd.Name = "GrantSearch"
 cmd.ActiveConnection = cnn
 cmd.CommandText = "SELECT fldPIN, fldProjectName, fldExpirationDate FROM tblPlanning WHERE fldPIN = ?"

'Input parameter
 Set paramPIN = cmd.CreateParameter(, adVarChar, adParamInput, 50)
 paramPIN.Value = Me.txtPIN
 cmd.Parameters.Append paramPIN

'Populate and enumerate through recordset
 Set rst = cmd.Execute
 If rst.EOF Then
    MsgBox "Nothing found...", vbInformation + vbOKOnly
    Exit Sub
 Else
    For Each var In myConnectedFields
        If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
    Next
 End If

All we're doing here is SELECTing two columns based on one input parameter. If I had a more complex WHERE requiring more parameters then the amount of coding grows by three lines for every parameter.

Perhaps one way around this in a SQL Server environment is to go the route of what I do in DAO: write a stored procedure that accepts a parameter and EXEC the stored procedure from here? I'm going to try this and report back.

[NeoPa]

Absolutely S7. I have changed it retrospectively , but I was in error exactly as you found. Thanks for that :-)

Thanks also for the link. I think that fills in the gaps that I wasn't able to provide here.

[NeoPa]

patjones:
Perhaps it's a question of my never having needed functionality in one that is not available in the other. I mean, if you're doing something like connecting to the data source and populating and enumerating through a recordset on the client side - I can't see an advantage in one over the other.

If you keep your exposure as limited as that then you wouldn't see it. As mentioned earlier, it is generally accepted that ADODB is better for handling non-Jet record sources (as you seem to be doing in your code). If you were to try to work within the current database though, the first thing you'd notice is that CurrentDb is a DAO specific object (ADODB doesn't support a Database object at all). This is covered in the linked article I believe.

Generally though, a single example is not very good for illustrating why something doesn't pertain. If I see a green man run across the room, I can state categorically that he was there (barring hallucinogens). If I don't, I'm not in the same position in regard to his absense. I can only say I didn't see him.

[patjones]

NeoPa:
Generally though, a single example is not very good for illustrating why something doesn't pertain.

My idea is to present the DAO method also when I get a chance and compare. I ought to put them in the same post really.

[NeoPa]

I don't think I'm getting my point across very well. Let me try again.

There is stuff that DAO does better (broadly speaking some Jet specific stuff). There is stuff that ADODB does better (broadly speaking some of the stuff related to external databases; ODBC; etc). There is stuff where they overlap and each is as good as the other (broadly speaking most of it). No example will help you understand where the boundaries lay, as any example will tend to fit within one of these areas. Think of a Venn diagram where the two sets of DAO and ADODB overlap. An example that fits within the ADODB set, as yours does from the fact that it's using SQL Server, will never illustrate that are defined as DAO but not ADODB. That doesn't mean that section doesn't exist, but simply that your example doesn't fit into it.

Does that make more sense?

[patjones]

It does make sense, or I should say rather that it is plausible to me. We're talking in very broad terms here (as you explicitly mention in your last post) - so there aren't really any specifics for me to make sense of in the first place. It's possible that I just don't understand what DAO and ADO are. The only points of reference I have are what ADO looks like in code, and what DAO looks like in code.

With that in mind, I wrote the DAO version of what I laid out in post #4:

Dim qdf As QueryDef
Dim qdfParam As QueryDef
Dim rst As Recordset

Set qdf = CurrentDb.CreateQueryDef("GrantSearch", "PARAMETERS [PIN] TEXT; SELECT fldPIN, fldProjectName, fldExpirationDate, fldLastModifiedBy FROM tblPlanning WHERE fldPIN = [PIN];")
qdf.Connect = "ODBC; DRIVER=SQL Server; SERVER=XXX; DATABASE=GrantsWatchList; UID=XXX; PWD=XXX"

Set qdfParam = CurrentDb.QueryDefs("GrantSearch")
qdfParam("PIN") = Me.txtPIN

Set rst = qdfParam.OpenRecordset
If rst.RecordCount = 0 Then
   MsgBox "Nothing found...", vbInformation + vbOKOnly
   Exit Sub
Else
   For Each var In myConnectedFields
       If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
   Next
End If

This actually bears some similarities to the previous code. The difference is that it doesn't work. It stops at qdfParam("PIN") = Me.txtPIN and tells me that the item is not in the collection (it's referring to the parameter "PIN").

Now, I have successfully used an ODBC connection string with a DAO QueryDef just as I'm trying to do here. The difference is that my successful instances are EXECing a stored procedure on the server, so I'm not trying to assign any parameter as in the code above:

Dim qdf As QueryDef
Dim rst As DAO.Recordset
Dim var As Variant

Set qdf = CurrentDb.CreateQueryDef("GrantSearch")
qdf.Connect = "ODBC; DRIVER=SQL Server; SERVER=XXX; DATABASE=XXX; UID=XXX; PWD=XXX"
qdf.ReturnsRecords = True
qdf.SQL = "EXEC sproc_GetGrant '" & Me.txtPIN & "'"

Set rst = qdf.OpenRecordset
If rst.RecordCount = 0 Then
    MsgBox "Nothing found...", vbInformation + vbOKOnly
    Exit Sub
Else
    For Each var In myConnectedFields
        If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
    Next
End If

This code works.

So, to bring this around to the beginning of my post, I am hoping to gain some more insight into the two interfaces by figuring out why the code in post #4 and the stored procedure code here works, but why the parameterized code above does NOT work.

If I've managed to confuse you further, I apologize.

[NeoPa]

DAO and ADODB are two separate, but similar, libraries (references) for handling data. Not all things supported by DAO are supported by ADODB, and vice versa.

If we're moving from a general discussion towards a more specific quetion (IE. Why is your DAO code not working?) then that's another matter. The failing code is not something that works in the ADODB version, as the ADODB library doesn't even support that approach.

I can't say I'm the best person to help with the specific question, but I might be able to make a couple of observations :

1. Line #3 should use DAO.Recordset.
2. CurrentDb is a function call and should be used once only. Typically the advice generally given is to use a DAO.Database variable at the start.
3. qdf may need setting to Nothing after creation to ensure it's creation is completed. I'm not sure. A With statement would handle that nicely. This is a long shot certainly, as line #8 reported no problems.
4. Use the Watch pane or other debug facility to see the state of the [GrantSearch] query and its parameters before running line #9. What you find will help direct further search towards the problem in your code.

I hope one/some of these help to narrow down the problem.

[patjones]

Okay, thanks - I'm going to check out the items on your list and see what happens. Apart from the code that I've posted already, I have no problem keeping the discussion general.

[TheSmileyCoder]

Looping through recordsets with .FindNext and .FindFirst where the records are retrieved from a ACCESS backend, I have found that DAO seems significantly faster.

That said, It may be at the time, I did not use the ADODB recordset properly, since I did not have that much experience. Since then I have restricted my recordsets to DAO.