Efficient Recordset

Question

Hello,

We are in the process of converting an Access 2003 database with large VBA code to 2007 version. I use the below code as a typical way in VBA to get a recordset:

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection

Set rs = New ADODB.Recordset

With rs

Set .ActiveConnection = cn

.Source = "Select top 5 * from tblTest;"

.LockType = adLockOptimistic

.CursorType = adOpenKeyset

.CursorLocation = adUseServer

.Open

End With

'Do some stuff here...

If rs.State = 1 Then rs.Close

Set rs = Nothing

Set cn = Nothing

My questions:

1. Is the above recordset the most efficient - vs. other methods (i.e. OpenRecordset)? Is one method preferred over other?
2. Do I have to convert the ADO to DAO when converting the database to 2007, or just continue using ADO?

Thanks,

Mike-

Answer 1

While I am a proponent of using DAO over ADO, I would never recommend you rewrite. You have my blessing to keep things as-is.

Performance-wise, I would be surprised if you could show a 10% difference one way or another. Let it go  :-)