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.

Heller

I get a type mismatch error on the OpenRecordset method. I'm using the code as shown above, no DAO or ADODB qualifiers. – Heller

If you are in a code window, opening Tools/References from the menu bar gets you to the Add References dialog. – Ledaledah

Answer 1

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

Ledaledah

Can't I just use some kind of CurrentDB qualifier instead of having to use connection strings since everything I want to query is contained within the Access database? – Heller

Ok, don't I have to add a reference to DAO though? I can't find anywhere to do that from within Access 2010. – Heller

Try the ADO one I added, above. – Ledaledah

Yeah, that worked for me and works best for me. It's all coming back to me now, for better or worse. – Heller

You don't absolutely need a DAO reference, but if you don't have it, you have to use Object data types for your variables (instead of Database and Recordset). But it's crazy to not have a DAO reference since you're using Access! – Dialyse

Answer 2

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.

Peplos

Thanks for the thorough explanation. – Heller

Answer 3

Decide if you want to use ADO or DAO? Here is a DAO (more native to Access/Jet) example

dim wrk as DAO.Workspace
dim db as DAO.Database

set wrk = DBEngine.Workspaces(0)
set db = wrk.OpenDatabase(CurrentDb.Name)
Dim rsSystem as DAO.Recordset

Dim sSQL As String

sSQL = "SELECT * FROM Table"
Set rsSystem = db.OpenRecordSet(sSQL, dbOpenDynaset)

exitRoutine:
If Not (db Is Nothing) Then
     db.Close
     Set db = Nothing
End If
Set wrk = Nothing

Not sure what you want to do with this recordset.

Chump

I don't see DAO in the contextual list when I try to add it, I'm sure it's just a reference I need to add to the MDB but for the life of me I cannot find references anywhere in the Access 2010 IDE – Heller

Answer 4

Dim rsSystem As Recordset

Both the ADO and DAO object models include Recordset objects. You can't interchange them.

Since you didn't specify which you wanted, yours could be an ADO Recordset ... which would account for the type mismatch error on the OpenRecordset method.

Set rsSystem = CurrentDB.OpenRecordset(sSQL)

That method returns a DAO Recordset, so first declare rsSytem as such.

Dim rsSystem As DAO.Recordset

Predikant

Yeah, thanks. @Robert's solution above works best for me. All this Access stuff is starting to come back to me now. – Heller

If I'm understanding your comment above, you decided to go with ADO, which is simply misguided. It does reflect what MS was promoting 10 years ago, but they were wrong to do so, and have since stopped promoting ADO over DAO for use in Access with Jet/ACE/ODBC data. – Dialyse

Answer 5

"Table" is a reserved word in SQL. If you must name your table "table", then enclose it in square brackets: "SELECT * FROM [Table]".

Jeanejeanelle