What record model to use for new projects? 1

[qlark]

Trying to determine the best solution for moving forward with VBA record finds.

Some literature state that dlookup is slow on larger tables ... I tested this against a 200 record DAO recordset findfirst and the DAO model was about 25% faster ... based on the coment I gather this will change for the worst as the size of the table increases

Another source stated that MS is no longer going to support new changes to the DAO model and they recommend moving to the ADO model.

Can I get another opinion on this last comment ... I have been using the DAO model for several years now and it seems to do everything I need ... I went to do a simple test with ADO and couldn't manage to get it to work so kind of hard to make my own mind up.

dim rst as ADOB.recordset
with rst
.open "sometable", CurrentProject.connection
.find "[somefield] = '" & somevalue & "'"

if not .eof then
debug.print "Found"
else
debug.print "Not Found"
end if
.close
end with

 

[PHV]

Before playing with an object you must at least instantiate it ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[qlark]

Ok Humor me please

The examples from your link to Microsoft site (links via the ADO - DAO conversion FAQ) show the DAO model with the
Dim and SET statments. I am assuming Set is doing the instantiation. The ADO statement appear to just goes and start using the object RST from the DIM statement ... no SET so I guess I am not following where the instantiation takes place in the ADO

I found another example that attaches an SQL (select string) to the DIM statement is that what I am missing?

dim rst as ADOB.recordset, SQLstr

SQLstr = "SELECT * FROM sometable;"

rst.open SQLstr, CurrentProject.Connection

 

[MajP]

not sure what that is. Looks like a mistake. It should be
dim rst as New adodb.recordset
dim sqlStr as string

You can list multiple variables of the same data type on the same line,but not different variable types
dim strSql, strSomethingElse as string. Also if you use the new keyword in the declaration in this case you declare and instantiate at the same time. Or do it explicitly


Yes if you are using DAO now, you should start changing over to ADO. It is like playing albums and casettes. They still work, but I would go CD or MP3. Same thing with people who use macros instead of VBA. They will eventually not be supported. The weird thing however is that ADODB still has some shortcomings in the model, even though they are pushing everyone in that direction already.

 

[qlark]

OK so after a few grey moments I finally found a snippet that works

dim rs as adodb.recordset
set rs = new adodb.recordset ' the object instantiation
rs.open "Select * from sometable where somefield = '" _
& someval & "'", CurrentProject.connection

if not rs.eof then debug.print rs!someotherfield

rs.close

Now comparing this code to the DAO code I found the ADO code was about 10% slower ... I also was struggling with the rs.find and rs.movefirst ... just could not get those to work for me ... so for the moment I guess I will stick with what works for me ... thanx for your time.

 

[MajP]

Yeah I think Beta was faster then VHS, but that does not mean much.

"If your building a new application we recommend that you use ADO,... because DAO probably won't ever get the new functionality that's now being added to ADO."
(page 194, A2K Desktop Developers Handbook)

 

[RoyVidar]

Well, think of it this way
- in a2k, the reference to DAO was removed
- in a2k2, the reference to DAO was still gone
- in a2k3, the reference to DAO as well as ADO is on by default

also, consider, as long as you're not working with ADPs or assigning ADO recordsets to forms, the form recordsets are DAO - all versions - so you will have to work with DAO anyway ...

also, do take a read elsewhere - some of the really, really good Access developers have never stopped using DAO - because they simply consider it the best tool when working with Jet data - some/a lot of them even prefers working with DAO over ADO when using SQL server as backend!

Since the arrival of a2k, the "Death of DAO" has been proclaimed - now in beginning of 2006, it would be fair to say that the rumors of it's death is slightly exaggerated;-)

I would go so far as to say that I believe ADO and DAO will die at the same time, to be replaced by ADO.Net which is a completely different animal, or perhaps even a newer method/library when the Office suite is more "DotNetIFied"

In the mean time, base the selection of your toolbox to what you're going to do.

If your next project is going to be Jet all the way, DAO is faster and better (especially since you have prior knowledge - just have to say, with some knowledge in ADO, you can really make that fly too - for instance, have a look at this technique on parameterized queries ADO twice as fast as DAO against native jet?)

If your next project is connecting to other platforms, or might be doing so in foreseeable future - get your behind to the nearest bookstore and start reading ;-)

Roy-Vidar

 

[perrymans]

Keep in mind that the speed and benefits of ADO is not measured solely in the time it takes to access X number of records.

The key difference is that ADO opens a connection, grabs the information, then stores it in LOCAL memory. This means that recordset is sitting in you RAM. So while it may take the same amount of time to build the recordset, ADO will be much faster to work with that recordset since it is pulling from memory, and not reaccessing the tables each time you change records.

Also, but holding those records in memory, when you finally do update, it is instantaneous. This connect-use-reconnect-update is an easy way to prevent record conflicts and problems with locking.

People put a lot of special effort into making DAO act unbound when ADO does it by design.

As for myself, I use both, mostly DAO because it is familiar, but for the simple things I do, ADO seems to be just as easy. I will say that my Treeview controls were taking 16 seconds to load in DAO, and only 3 seconds in ADO (10,000+ records and 3-6 levels). That was enough for me.

Sean.

Sean.