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

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

Answer 2

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.

Answer 3

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 4

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

Answer 5

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