Convert DAO to ADO (1 Viewer)

Zydeceltico · Apr 10, 2019

Hi all -

I am wading into Recordsets and trying to learn how to use them.

I am seeing some examples that use DAO and others that use ADO and it doesn't appear that the syntax is the same which is - of course - confusing.

I already have a substantial amount of DAO.Recordset instances in my db and everything works great - because I was "unaware" until just this very moment that ADO may be a better fit for what I am doing.

Since our company's ERP is SQL Server based I think it would be prudent to make sure that what I am coding at least has the opportunity to "play well" with the ERP in the future.

Can someone tell me the basic differences between the two so I can get an idea of if it is worth working through or not?

As an example I use the following quite often as boiler plate:

Code:

  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    Set rs = Me.Recordset
    rs.FindLast "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs

How would I need to modify that to be ADO compliant? And would I need to learn a whole new set of methods? (e.g., FindFirst, FindLast, etc.)

Thanks,

Tim

theDBguy · Apr 10, 2019

Hi Tim. This link gives you a little bit of description about ADO, and this link lists all the properties and methods you can use with it. And for your specific question, you can check out the Find Method.

NauticalGent · Apr 10, 2019

Have a look at a series of YouTube videos from Steve Bishop. He delves into this very issue.

If I understand it, the mile high answer is that SQL does not have to convert the ADO syntax like it does DAO and it is that overhead that makes ADO a better(?) fit for SQL.

I have also heard from others on this forum that the performance "boost" is negligible and all but imperceptible to users.

I could be wrong on that part, but I think I remember correctly. At any rate, give those videos a look over - you just might learn something useful!

Zydeceltico · Apr 10, 2019

Thanks for the info!

I also found this which gives a relatively extensive comparison of details.

http://www.utteraccess.com/wiki/Choosing_between_DAO_and_ADO

Judging by what I am reading, thinking about the minimal scope/exposure our QC DB will have to the company ERP, and considering we're using Access 2016, I believe I'll just leave everything as is. We should be fine.

Uncle Gizmo · Apr 10, 2019

Zydeceltico said:
I believe I'll just leave everything as is. We should be fine.

When I first saw your question that was my immediate thought, just leave it as it is. However there is an issue that may become relevant in certain circumstances. Whether you use ADO or DAO may depend on which version of MS Access the code will be used in. Some versions of MS Access use ADO by default, and some versions use DAO by default. Now you might say this is not a problem because you can easily set a reference to whichever version you are using (Or to both). I recall a case where a Company's IT policy was set up so that you could not set references in your MS Access database. In that happenstance then the code will need to be written for whichever version that the particular MS Access uses.

MajP · Apr 10, 2019

If you are working with native Access tables there is no reason not to use DAO. It is optimized for Jet. If working external datasources then ADO is likely the way to go. And there is no reason you cannot do both in the same database, DAO for the local stuff and ADO for the external stuff.

Zydeceltico · Apr 10, 2019

MajP said:
If you are working with native Access tables there is no reason not to use DAO. It is optimized for Jet. If working external datasources then ADO is likely the way to go. And there is no reason you cannot do both in the same database, DAO for the local stuff and ADO for the external stuff.

Thanks. That's what I am gathering and what I am planning. Our likely scope overlay will be limited and probably most easily dealt with by exporting chunks of data from the ERP and analyzing via Access anyway. When I say "chunks" I am actually talking about what would be tiny crumbs to most of you heavy hitters.

CJ_London · Apr 10, 2019

within access I believe the main advantage of ado is that you can have disconnected record sets. This means that if you are using an enterprise rdbms you can keep your connection time to a minimum, reducing the risk of hackers riding the connection into the server. Further communicating with the server is only done and granted via stored procedures.

You can assign the returned record set to the form recordset - and list/combo record sets , do what you want with the data then run another stored procedure to update the back end. You can sort and filter the record set, but not with the right click menu which only works with dao.

You will also find that ado filter options are not as rich as dao.

Data clashes where two users have updated the same record at the same time would be handled within sql server

Convert DAO to ADO (1 Viewer)

Zydeceltico

Registered User.

theDBguy

I’m here to help

NauticalGent

Ignore List Poster Boy

Zydeceltico

Registered User.

Uncle Gizmo

Nifty Access Guy

MajP

You've got your good things, and you've got mine.

Zydeceltico

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread