By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Discussion: Pros and Cons of DAO v ADODB

patjones
Expert 100+
P: 931
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
Dec 8 '11 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 8 '11 #2

Expert 100+
P: 446
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\ADO\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
Dec 9 '11 #3

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Dim cnn As New ADODB.Connection
  2. Dim cmd As New ADODB.Command
  3. Dim paramPIN As New ADODB.Parameter
  4. Dim rst As ADODB.Recordset
  5. Dim var As Variant
  6.  
  7. 'Define and open connection
  8.  cnn.ConnectionString = "PROVIDER=SQLOLEDB; DRIVER=SQL Server; SERVER=XXX; DATABASE=XXX; UID=XXX; PWD=XXX"
  9.  cnn.Open
  10.  
  11. 'Define ADO command
  12.  cmd.Name = "GrantSearch"
  13.  cmd.ActiveConnection = cnn
  14.  cmd.CommandText = "SELECT fldPIN, fldProjectName, fldExpirationDate FROM tblPlanning WHERE fldPIN = ?"
  15.  
  16. 'Input parameter
  17.  Set paramPIN = cmd.CreateParameter(, adVarChar, adParamInput, 50)
  18.  paramPIN.Value = Me.txtPIN
  19.  cmd.Parameters.Append paramPIN
  20.  
  21. 'Populate and enumerate through recordset
  22.  Set rst = cmd.Execute
  23.  If rst.EOF Then
  24.     MsgBox "Nothing found...", vbInformation + vbOKOnly
  25.     Exit Sub
  26.  Else
  27.     For Each var In myConnectedFields
  28.         If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
  29.     Next
  30.  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.
Dec 9 '11 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 9 '11 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 9 '11 #6

patjones
Expert 100+
P: 931
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.
Dec 9 '11 #7

NeoPa
Expert Mod 15k+
P: 31,186
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?
Dec 9 '11 #8

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2. Dim qdfParam As QueryDef
  3. Dim rst As Recordset
  4.  
  5. Set qdf = CurrentDb.CreateQueryDef("GrantSearch", "PARAMETERS [PIN] TEXT; SELECT fldPIN, fldProjectName, fldExpirationDate, fldLastModifiedBy FROM tblPlanning WHERE fldPIN = [PIN];")
  6. qdf.Connect = "ODBC; DRIVER=SQL Server; SERVER=XXX; DATABASE=GrantsWatchList; UID=XXX; PWD=XXX"
  7.  
  8. Set qdfParam = CurrentDb.QueryDefs("GrantSearch")
  9. qdfParam("PIN") = Me.txtPIN
  10.  
  11. Set rst = qdfParam.OpenRecordset
  12. If rst.RecordCount = 0 Then
  13.    MsgBox "Nothing found...", vbInformation + vbOKOnly
  14.    Exit Sub
  15. Else
  16.    For Each var In myConnectedFields
  17.        If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
  18.    Next
  19. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2. Dim rst As DAO.Recordset
  3. Dim var As Variant
  4.  
  5. Set qdf = CurrentDb.CreateQueryDef("GrantSearch")
  6. qdf.Connect = "ODBC; DRIVER=SQL Server; SERVER=XXX; DATABASE=XXX; UID=XXX; PWD=XXX"
  7. qdf.ReturnsRecords = True
  8. qdf.SQL = "EXEC sproc_GetGrant '" & Me.txtPIN & "'"
  9.  
  10. Set rst = qdf.OpenRecordset
  11. If rst.RecordCount = 0 Then
  12.     MsgBox "Nothing found...", vbInformation + vbOKOnly
  13.     Exit Sub
  14. Else
  15.     For Each var In myConnectedFields
  16.         If Not IsNull(rst(var).Value) Then Me(var) = rst(var)
  17.     Next
  18. 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.
Dec 9 '11 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 10 '11 #10

patjones
Expert 100+
P: 931
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.
Dec 12 '11 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 12 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.