Top Contributors

Just how much faster is ADO than DAO with a SQL Server backend?

While I was looking at bitsqueezer's lengthy contributions to this Q, I was looking to test his assertion about the use of DCount being faster than other methods (BTW I don't agree with his assertion, but that's a different discussion)  The form I started to look at didn't have the total records bit that I was think was there, but it's a weighty form with a perceptible pause in navigating so I figured I look to optimize its Current event.

There were a couple of DCounts in there and I eliminated them in favor of a "Select Count(PK)..." and testing with a block like
Dim TheTime As Single
TheTime = Timer
'code to test
TheTime = Timer - TheTime
MsgBox TheTime  

And the recordset code was faster.

And then I broke down what parts of the event were taking up all the time.
The majority of it was being chewed up by tweaking the SQL on a saved passthrough query, running it, and mucking with controls based on the results.  So I had a look at the sproc behind it, and tweaked that a little.

Still, it was about a quarter-second to get that all done.

So I figured, what the hell, let's hash out the ADO code to bypass the need to tweak the saved query and execute it in favor of ADO just getting it done.  Let's see what we get.  MS suggested DAO these days, things are going to be comparable, right?

Yeah, not so much!
Where the DAO code took about 25/100ths of second to execute, the ADO code to 2/100ths
That's a full order of magnitude faster!
Is that to be expected?  Is that typical?  Most folks go ADO or DAO and never mix the two.
Who else has similar experiences?
View Previous CommentsLoad All Comments (7)
Rank: Genius

Author Comment

Nick672015-01-07 09:35 AMID: 140587
Microsoft only suggests DAO for JET/ACE DB's; that's what DAO is good at.   It's not good for anything else.
ADP's have been deprecated.  The recommendation these days is linked tables, which are a DAO technology at their heart.
OLEDB however is on it's way out
You hear that SQL Server is going to stop supporting OLEDB and yet at the same time that it is not going away in Access.
It's confusing to say the least.
Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2015-01-07 09:47 AMID: 140592
It is going away in Access as well as everything else.   Microsoft is moving back to ODBC for everything.

 and let's not mix things up here; ADP's were a native interface to SQL, which really has nothing to do with DAO vs ADO.  They decided to use ADO connections as part of ADP's, but that's only because they wanted to use OLEDB, which DAO knows nothing about.  DAO only knows ODBC.  

 They could if they had wanted to build ADP's on top of DAO, but then they would have been stuck with ODBC.

Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2015-01-07 09:50 AMID: 140593
<<It is going away in Access as well as everything else.   Microsoft is moving back to ODBC for everything.>>

 Actually, to be more precise, it's not going away in Access as much as it will never be used again.  The desktop side of Access will remain right where it is.   Microsoft's sole focus is Access Web Apps.

 At some point, you simply won't be able to use OLEDB with a datasource (you can still use ADO, as ADO does have ODBC capability, but for Access, it would be simpler to use DAO and linked tables).

Rank: Genius

Author Comment

Nick672015-01-07 09:55 AMID: 140594
The source of confusion is here
Making sense out of this as an Access guy is tough
Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2015-01-07 10:18 AMID: 140597
Yeah, I can understand where it would be confusing.

  Simply put:

1. OLEDB is going away.
2. You can continue to use ADO, but you will need to use the ODBC Provider once OLEDB is gone.
3. You can continue to use DAO (which uses ODBC), *but* you may not have access to all the features of a data source.
4. If you need DAO in a 64 bit situation, you simply use the Access Connectivity  Engine (ACE) object libs, which for all intents and purposes is DAO - they just don't call it that anymore<g>.
Rank: Genius

Author Comment

Nick672015-01-30 08:53 AMID: 143188
Now, what the hell did MS do with the January 2015 patches?!?
This connection string worked swimmingly well until mid-January 2015
Provider=SQLNCLI11;Server=ServerName\Instance;Database=myDataBase; Trusted_Connection=yes;
Suddenly, all boxes except the server started throwing error 3706 'the provider does not exist. It may not be properly installed.'
I've had to switch it to
Provider=SQLOLEDB;Data Source=ServerName\Instance;Initial Catalog=myDataBase;Integrated Security=SSPI

And at the same time, I've got 'The command SaveRecord isn't available at this time' errors popping up on forms that haven't been tweaked for months.  Very strange.  Just as I go to use ADO in production, something busts it good and proper.