Open Record Set in Access 2003/2007

Question

Years have passed since I worked on the access code, and that makes me completely insane.

I just don’t remember anything, and all I want to do is open a set of records in the code and nothing was found on the Internet or some kind of permutation of the code I tried works.

In short:

Dim rsSystem As Recordset Dim sSQL As String sSQL = "SELECT * FROM Table" Set rsSystem = CurrentDB.OpenRecordset(sSQL) 

What will I miss in holy hell?

Thanks in advance.

Answer 1

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

The easiest way is to use DAO in 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 

Answer 2

If you declare only a Recordset without specifying whether it is a DAO or ADO, Access will determine whether it will be a DAO or ADO, depending on the order of your links:

Open the code window, open "Tools" → "Links" and browse the list. It will look something like this:

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

If you declare your recordset without specifying a type, Access selects the first of these links (= the one that is larger at the top of the list) and creates a recordset of this type.
Therefore, in this example, it will be DAO.Recordset .

Now back to your question:
You declare your recordset without specifying a type.
Therefore, if the first link in your Access database is ADO, Access will create ADODB.Recordset .
Then you open it using the DAO method, which expects DAO.Recordset , and why you get the error.

There are two ways to solve your problem:

• Make sure that the Access database has a link only to ADO or DAO (but not both), then you do not need to specify the type of record set.
• If you really need both links, always list your entries as DAO.Recordset or ADODB.Recordset to make sure that this is really the type that your code expects.

Answer 3

 Dim rsSystem As Recordset 

Both ADO and DAO object models include Recordset objects. You cannot exchange them.

Since you did not specify what you wanted, yours could be an ADO ... recordset that would take into account a mismatch error such as the OpenRecordset method.

 Set rsSystem = CurrentDB.OpenRecordset(sSQL) 

This method returns a set of DAO records, so first declare rsSytem as such.

 Dim rsSystem As DAO.Recordset 

Answer 4

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

Answer 5

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

 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 set of records.