What is the difference between 2 ways to create a RECORDSET

[eyal7773]

Hello,
I would like to know What is the difference between 2 ways to create a RECORDSET.

Until 2007 I used to create it like this :
--------------
Dim db1 As Database
Dim rec1 As Recordset
Set db1 = CurrentDb
Set rec1 = db1.OpenRecordset(strSQL, dbOpenDynaset
-----------------

But when 2007 arrived, I read Sams Elison book and then I started to create it like this:
----------------
Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnX
myRecordSet.Open SQLstatement
----------------

WHAT IS THE DIFFERENCE ? is there one way that is more quick ? More efficient ?
Thanks.

 

[boblarson]

I would say that the difference is that DAO is the native way that Access uses for things so that is the better choice in this instance. The ADO is really a translation layer which you really don't need internally. You might want to use ADO if connecting to a SQL Server (but even then DAO can handle most things).

All your code needs is a little modification and it should take care of you.

Dim db1 As DAO.Database
Dim rec1 As DAO.Recordset
Set db1 = CurrentDb
Set rec1 = db1.OpenRecordset(strSQL, dbOpenDynaset

 

[Banana]

Boy, if I get a nickel for every time a "DAO vs. ADO" question...

Always a good question, nonetheless. See if this article helps clarify some of the backgrounds, whys and when to choose

I personally tend towards 95% DAO, 5% ADO (yes, you can mix both DAO and ADO in same project) for projects that involve linked ODBC sources; I don't usually bother with ADO for Access-only projects.