Open recordset in Access 2003/2007

Question

It's been years since I've worked in Access code and it is making me certifiably insane.

I just don't remember anything and all I want to do is open a recordset in code and nothing I've found online or any permutation of code I've tried works.

The short of it:

Dim rsSystem As Recordset
Dim sSQL As String

sSQL = "SELECT * FROM Table"
Set rsSystem = CurrentDB.OpenRecordset(sSQL)

What in the holy hell am I missing?

Thanks in advance.

Answer 1

If you declare just a Recordset without specifying if it's DAO or ADO, Access will decide on its own whether it will be DAO or ADO, depending on the order of your references:

Open a code window, go to Tools --> References, and look at the list there.
It will look something like that:

You see that in this example, there is a reference on DAO ("Microsoft DAO 3.6 Object Library") and ADO ("Microsoft ActiveX Data Objects 2.5 Library").

If you declare your Recordset without specifying the type, Access picks the first of these references (=the one that's more on top of the list) and creates a Recordset of this type.
So in this example, it will be a DAO.Recordset.

Now back to your question:
You declare your Recordset without specifying the type.
So if the first reference in your Access database is ADO, Access will create an ADODB.Recordset.
Then you open it with a DAO method, which expects a DAO.Recordset, and that's why you get the error.

There are two ways to solve your problem:

1. Make sure that your Access database only has a reference to ADO or DAO (but not both), then you don't need to specify the type of the recordset.
2. If you really need both references, always declare your recordsets as DAO.Recordset or ADODB.Recordset to make sure that it's really of the type that your code expects.

Answer 2

Examples here, for all permutations of opening a "Recordset": http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp

The easiest way is to use DAO on the current database. My VBA is a little rusty, but...

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = DB.OpenRecordset("table or query name")

For ADO:

Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, adOpenDynamic