vba 在 Access 2003/2007 中打开记录集

Open recordset in Access 2003/2007

提问by Tom

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

自从我从事 Access 代码工作已经有很多年了，这让我确信无疑是疯了。

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.

提前致谢。

采纳答案by Robert Harvey

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

这里的例子，对于打开“记录集”的所有排列：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...

最简单的方法是在当前数据库上使用 DAO。我的 VBA 有点生锈，但是...

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

For ADO:

对于 ADO：

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

回答by Christian Specht

If you declare just a Recordsetwithout 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:

如果您只声明 aRecordset而不指定它是 DAO 还是 ADO，Access 将自行决定它是 DAO 还是 ADO，具体取决于您的引用顺序：

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") andADO ("Microsoft ActiveX Data Objects 2.5 Library").

您会看到在此示例中，有一个关于 DAO（“Microsoft DAO 3.6 对象库”）和ADO（“Microsoft ActiveX 数据对象 2.5 库”）的引用。

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.

如果在未指定类型的情况下声明 Recordset，Access 将选择这些引用中的第一个（=位于列表顶部的那个）并创建此类型的 Recordset。
所以在这个例子中，它将是一个DAO.Recordset.

Now back to your question:
You declare your Recordset without specifying the type.
So if the first reference in yourAccess 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.

现在回到你的问题：
你声明你的 Recordset 而不指定类型。
因此，如果在第一参考你的Access数据库ADO，Access将创建一个ADODB.Recordset。
然后你用一个 DAO 方法打开它，它需要一个DAO.Recordset，这就是你得到错误的原因。

There are two ways to solve your problem:

有两种方法可以解决您的问题：

1. Make sure that your Access database only has a reference to ADO orDAO (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.Recordsetor ADODB.Recordsetto make sure that it's really of the type that your code expects.

1. 确保您的 Access 数据库只引用 ADO或DAO（但不是两者），然后您不需要指定记录集的类型。
2. 如果您确实需要这两个引用，请始终将您的记录集声明为DAO.Recordset或ADODB.Recordset以确保它确实是您的代码所期望的类型。

回答by JeffO

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

决定是要使用 ADO 还是 DAO？这是一个 DAO（更原生于 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 recordset.

不确定你想用这个记录集做什么。

回答by HansUp

Dim rsSystem As Recordset

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

ADO 和 DAO 对象模型都包含 Recordset 对象。你不能互换它们。

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.

由于您没有指定所需的内容，您的可能是 ADO Recordset ...这将解释 OpenRecordset 方法上的类型不匹配错误。

Set rsSystem = CurrentDB.OpenRecordset(sSQL)

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

该方法返回一个DAO记录集，因此首先声明 rsSytem。

Dim rsSystem As DAO.Recordset

回答by harfang

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

“表”是 SQL 中的保留字。如果必须将表命名为“表”，请将其括在方括号中："SELECT * FROM [Table]".