vba 在 Access 2007 中使用 ADO 还是 DAO 更好？

Is it better to use ADO or DAO in Access 2007?

提问by Andrew Scagnelli

When creating a new database in Access 2007, should ADO (ActiveX Data Objects) or DAO (Data Access Objects) be used?

在 Access 2007 中创建新数据库时，应该使用 ADO（ActiveX 数据对象）还是 DAO（数据访问对象）？

Edit: Part of this database will be importing data from Excel 2007 spreadsheets.

编辑：此数据库的一部分将从 Excel 2007 电子表格中导入数据。

采纳答案by onedaywhen

[For the record, the official name for what once was 'Jet' is now the 'Access database engine'.]

[为了记录，曾经是“Jet”的官方名称现在是“Access数据库引擎”。]

For ACE (the Access2007 engine .accdb format) features it has to be ACEDAO.

对于 ACE（Access2007 引擎 .accdb 格式）功能，它必须是 ACEDAO。

For Jet 4.0 features it has to be ADO classic.

对于 Jet 4.0 功能，它必须是 ADO 经典版。

For Jet 3.51 features and earlier, pick either ADO or DAO. There are advantages and disadvantages to both. The vast majority of Access database engine functionality is common to both; the mutually exclusive functionality is arguable fringe. A lifestyle choice, perhaps, but no big deal. The smart coder uses the best of both :)

对于 Jet 3.51 功能和更早版本，选择 ADO 或 DAO。两者都有优点和缺点。绝大多数 Access 数据库引擎功能对两者都是通用的；相互排斥的功能是有争议的边缘。也许是一种生活方式的选择，但没什么大不了的。智能编码器使用两者中最好的 :)

I've used both quite a bit and ADO is my personal preference. It is more modern than DAO, so architecturally it is an improvement: flatter object model, none of the tear down problems of DAO, etc. More properties and methods and introduces events (DAO has none) e.g. for asynchronous connection and fetching of records. ADO recordsets can be disconnected, hierarchical and fabricated, DAO recordsets cannot. Basically, they took the good things about DAO and made them better.

我已经使用了很多，ADO 是我个人的偏好。它比 DAO 更现代，所以在架构上它是一个改进：更扁平的对象模型，没有 DAO 的拆卸问题等。更多的属性和方法并引入了事件（DAO 没有），例如用于异步连接和获取记录。ADO 记录集可以断开连接、分层和编造，而 DAO 记录集则不能。基本上，他们采用了 DAO 的优点并使其变得更好。

DAO is not without its strong points. For one, you will find more DAO code examples than ADO for Access/Jet.

DAO 并非没有优点。一方面，您会发现比 ADO for Access/Jet 更多的 DAO 代码示例。

P.S. for some reason, folk who like DAO really dislike ADO. Ignore the propaganda. ADO isn't deprecated. The ACE has an OLE DB provider and is currently the only way of using ACE in 64 bit. ADO.NET hasn't replaced ADO classic any more than VB.NET has replaced VBA6 in Access projects.

PS出于某种原因，喜欢DAO的人真的不喜欢ADO。无视宣传。ADO 没有被弃用。ACE 有一个 OLE DB 提供程序，并且是目前在 64 位中使用 ACE 的唯一方法。ADO.NET 并没有取代 ADO 经典版，就像 VB.NET 在 Access 项目中取代 VBA6 一样。

EDIT: just to clarify, "For Jet 4.0 features it has to be ADO classic," this is because DAO 3.6 only received a few enhancements for the features new to Jet 4.0. For example, for the DECIMALdata type you cannot specify scale/precision. Other features are missing completely from DAO. For example, can you do the following in Jet 4.0 using DAO (or ACEDAO in ACE for that matter)?

编辑：只是为了澄清，“对于 Jet 4.0 功能，它必须是 ADO 经典版”，这是因为 DAO 3.6 仅对 Jet 4.0 的新功能进行了一些增强。例如，对于DECIMAL数据类型，您不能指定小数位数/精度。DAO 完全没有其他功能。例如，您能否在 Jet 4.0 中使用 DAO（或 ACE 中的 ACEDAO）执行以下操作？

CREATE TABLE Test (
   col1 CHAR(4) WITH COMPRESSION DEFAULT '0000' NOT NULL, 
   CHECK (NOT EXISTS (
                      SELECT T1.col1 
                        FROM Test AS T1 
                        WHERE T1.col1 <> '0000' 
                        GROUP 
                           BY T1.col1 
                       HAVING COUNT(*) > 1
                      ))
);

(hint: compressible fixed-width text column with table-level data integrity constraint.) No, you cannot.

（提示：具有表级数据完整性约束的可压缩固定宽度文本列。）不，您不能。

AFAIK the only enhancements to ACEDAO was for the new ACE functionality i.e. they didn't go back and fill in the Jet 4.0 gaps in DAO. And why should they? We still have ADO to plug the gaps. Better that the team spent their time more productively, like fixing that annoying DECIMALsort bug, for me the best thing about ACE ;-)

AFAIK 对 ACEDAO 的唯一增强是新的 ACE 功能，即它们没有回过头来填补 DAO 中 Jet 4.0 的空白。他们为什么要这样做？我们仍然有 ADO 来填补空白。最好让团队更高效地花费时间，比如修复那个烦人的DECIMAL排序错误，对我来说 ACE 最好的事情 ;-)

回答by Albert D. Kallal

DAO is the is the recommended technology here. ADO has much been depreciated, and is now being replaced with ADO.net.

DAO 是这里推荐的技术。ADO 已经贬值很多，现在正在被 ADO.net 取代。

Not only is DAO the native and recommended data object model for using MS access, it continues to be enhanced and now has a whole bunch of new features for sharepoint. In access 2007 we now have Support for SharePoint lists. This means that new DAO object model for 2007 allows a sharepoint list to be used and viewed as a SQL server table. That means you can use SQL on sharepoint lists (in fac there not even a oleDB provider that allows you to use SharePoint lists this way, but with DAO you can now do this). There is nothing of this sort that been added to ADO. So SharePoint lists from a access (dao) point of view sees these SharePoint lists as a standard table.

DAO 不仅是使用 MS 访问的本机和推荐数据对象模型，它还不断得到增强，现在为 sharepoint 提供了一大堆新功能。在 Access 2007 中，我们现在支持 SharePoint 列表。这意味着 2007 年的新 DAO 对象模型允许使用共享点列表并将其视为 SQL 服务器表。这意味着您可以在共享点列表上使用 SQL（事实上，甚至没有一个 oleDB 提供程序允许您以这种方式使用 SharePoint 列表，但通过 DAO，您现在可以做到这一点）。ADO 中没有添加任何此类内容。因此，从访问 (dao) 的角度来看，SharePoint 列表将这些 SharePoint 列表视为标准表。

Furthermore DAO in access also has support for what we call complex data types. This was done to support XML lists from sharepoint. Keep in mind for the next version of access (2010) we are going to see a whole bunch more new additional features being added to DAO (JET is now called ACE).

此外，访问中的 DAO 也支持我们所谓的复杂数据类型。这样做是为了支持来自 sharepoint 的 XML 列表。请记住，对于访问的下一个版本（2010），我们将看到更多新的附加功能被添加到 DAO（JET 现在称为 ACE）。

So it is without question that DAO is the correct and good model to use. ADO is not receiving any more enhancements, and has been superseded by ADO.NET.

所以毫无疑问，DAO 是正确且好的使用模型。ADO 不再接受任何增强，并已被 ADO.NET 取代。

So the future belongs to DAO, and it's clear that's where Microsoft is investing its money in terms of MS access and terms of upgrading Access to work with things sharepoint.

所以未来属于 DAO，很明显，这就是微软在 MS 访问和升级 Access 以与 sharepoint 一起工作的条款方面投资的地方。

Access 2007 received multi-value capabilities for its field definitions, and again this was a result of enhancements for supporting sharepoint. However, these features are part of JET and these enhancements can be used without sharepoint. they are now part of DAO.

Access 2007 的字段定义获得了多值功能，这也是支持共享点的增强功能的结果。但是，这些功能是 JET 的一部分，并且可以在没有 sharepoint 的情况下使用这些增强功能。他们现在是 DAO 的一部分。

---

---

edit: Perhaps I'm going to expand on this a little bit, and try to clarify what we have such opposing answers here, I can assure you that when using access 2007, you're far better off to use DAO.

编辑：也许我会稍微扩展一下，并尝试澄清我们在这里有这样相反的答案，我可以向您保证，在使用 access 2007 时，您最好使用 DAO。

Where the confusion stems from, is if you look of the tools references when you choose to use the default data object model access 2007, the problem here is it's not called DAO anymore. It is now called ACE.

混淆的根源在于，如果您在选择使用默认数据对象模型访问 2007 时查看工具引用，这里的问题是它不再称为 DAO。它现在被称为ACE。

When you use DAO in access 2007 You'll note in the tool references, the reference is not set to DAO 3.6 ( that version has been depreciated, and also is now not part of MDAC download anymore). You'll notice that the new reference when using DAO in ms-access is called:

当您在 access 2007 中使用 DAO 时，您会在工具参考中注意到，该参考未设置为 DAO 3.6（该版本已经过时，现在也不再是 MDAC 下载的一部分）。您会注意到在 ms-access 中使用 DAO 时的新引用被称为：

Microsoft office 12.0 access database engine Object Library

Microsoft office 12.0 访问数据库引擎对象库

Now the above is a bit of a mouth full, but the above is the correct for reference access 2007 when you're going to use DAO in place of ADO.

现在上面的内容有点啰嗦，但是当您打算使用 DAO 代替 ADO 时，上面的内容对于参考 access 2007 来说是正确的。

In other words, perhaps we should call this DAO II.

换句话说，也许我们应该称之为 DAO II。

In other words, this data engine continues to be enhanced, and will most assuredly see a 64 bit version of this engine for office 2010 (office 14).

换句话说，这个数据引擎将继续得到增强，并且肯定会在 office 2010 (office 14) 中看到这个引擎的 64 位版本。

So the question or confusion centers around what term were going to use when we refer to using DAO in access 2007. The confusion here is in fact that the documentation and even the tools ->reference does not call it DAO.

因此，当我们在 Access 2007 中提到使用 DAO 时，问题或混淆集中在将使用什么术语上。这里的混淆实际上是文档甚至工具 -> 参考并未将其称为 DAO。

At the end of the day in access 2007, if you plan to use DAO then that means that you set the above reference, and do not set a reference to DAO 3.6. Regardless, it makes absolutely no sense to start using ADO now when it's been depreciated, and the new DAO object model for access continues to be enhanced and invested in by Microsoft.

在 access 2007 的一天结束时，如果您打算使用 DAO，那么这意味着您设置了上述引用，而不是设置对 DAO 3.6 的引用。无论如何，在 ADO 折旧后立即开始使用它绝对没有意义，并且 Microsoft 继续增强和投资用于访问的新 DAO 对象模型。

I hope this helps in clearing up the confusing here. While DAO/JET It's being depreciated, the new version access 2007 is based on the same code base, except it continues to get enhanced. So the new data engine in access can be considered and called the new DAO object model.

我希望这有助于消除这里的混乱。虽然 DAO/JET 正在贬值，但新版本 access 2007 基于相同的代码库，只是它继续得到增强。所以访问中的新数据引擎可以被认为是新的DAO对象模型。

I'm currently under NDA on this issue, but I can most surely tell you that for the next version of office (2010) we are going to see a whole slew of enhancements again.

我目前在这个问题上处于保密协议之下，但我可以肯定地告诉你，对于 Office 的下一个版本（2010），我们将再次看到大量的增强功能。

So it is near unanimous among Access developers that when developing access applications and using the native data engine, the preference here is to use the DAO object model ( but keep in mind we're not calling it that anymore, we called it ACE).

因此，Access 开发人员几乎一致认为，在开发访问应用程序和使用本机数据引擎时，这里的首选是使用 DAO 对象模型（但请记住，我们不再这样称呼它，我们将其称为 ACE）。

回答by David-W-Fenton

The answer to the question depends on what you're doing. If you're using Access to work with data that's in a format whose ADO interface is more versatile, then use ADO. If you're using Jet data, or using the Jet database engine to work with another database engine (via ODBC), then DAO is the right choice.

这个问题的答案取决于你在做什么。如果您使用 Access 处理 ADO 接口更通用的格式的数据，请使用 ADO。如果您使用 Jet 数据，或者使用 Jet 数据库引擎与另一个数据库引擎（通过 ODBC）一起工作，那么 DAO 是正确的选择。

But that answer assumes you're working from Access. If you're working from some other programming environment, the answers will likely be completely different.

但该答案假定您使用 Access 工作。如果您在其他编程环境中工作，答案可能会完全不同。

回答by Smandoli

It depends on your needs. Neither tool is expected to disappear soon.

这取决于您的需求。预计这两种工具都不会很快消失。

If you don't have experience in either ADO or DAO, you'll find DAO is much, much easier. So unless you need ADO, use DAO.

如果您没有 ADO 或 DAO 方面的经验，您会发现 DAO 容易得多。所以除非你需要 ADO，否则使用 DAO。

You added this critical item: "I'm trying to pull in data from an external source into an Access DB." This connectivity may require ADO.

您添加了这个关键项目：“我正在尝试将数据从外部源拉入 Access DB。” 这种连接可能需要 ADO。

回答by Scott Ivey

ADO is the current recommended access method. I think DAO has been deprecated for quite a number of years.

ADO 是当前推荐的访问方法。我认为 DAO 已经被弃用了很多年。

Looks like its been since Access 2000 - according to this link,

看起来它是从 Access 2000 开始的——根据这个链接，

List of obsolete data access technologies - http://msdn.microsoft.com/en-us/library/ms810810.aspx#mdactechnologies road map old_topic9

过时的数据访问技术列表 - http://msdn.microsoft.com/en-us/library/ms810810.aspx#mdac技术路线图 old_topic9

Quote from the above article, which was revised Dec 2008 - "Data Access Objects (DAO): DAO provides access to JET (Access) databases. This API can be used from Microsoft Visual Basic, Microsoft Visual C++, and scripting languages. It was included with Microsoft Office 2000 and Office XP. DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system."

引用自 2008 年 12 月修订的上述文章-“数据访问对象 (DAO)：DAO 提供对 JET (Access) 数据库的访问。此 API 可用于 Microsoft Visual Basic、Microsoft Visual C++ 和脚本语言。它是包含在 Microsoft Office 2000 和 Office XP 中。DAO 3.6 是该技术的最终版本。它不会在 64 位 Windows 操作系统上可用。”

回答by Brandon

DAO just rocks in terms of performance compared to ADO. There's no comparison.

与 ADO 相比，DAO 在性能方面表现出色。没有可比性。

回答by aristippus303

Apologies that this is an answer, when it should have been a comment (I do not have the rep), but I wanted to clear up an erroneous claim that DAO/ACEDAO does not support Jet 4.0 record-locking. It does, and that is the default behaviour, irrespective of what certain MS articles claim.

抱歉，这是一个答案，而它应该是评论（我没有代表），但我想澄清一个错误的说法，即 DAO/ACEDAO 不支持 Jet 4.0 记录锁定。它确实如此，而且这是默认行为，无论某些 MS 文章声称什么。

The problem is this may introduce huge bloat (hugely fragmented DB file) when using DAO edit/update and you cannot turn it off in DAO/ACEDAO.

问题是这可能会在使用 DAO 编辑/更新时引入巨大的膨胀（非常碎片化的 DB 文件），并且您无法在 DAO/ACEDAO 中将其关闭。

If you do have this issue, you can turn it off via first opening the database via an OLEDB connection using the correct Jet OLEDB:Database Locking Mode settings, which will allow you to set the database to page-level locking. This property will then be respected by consequent connections, DAO or otherwise, so you can then use DAO for fast updates etc.

如果您确实遇到了这个问题，您可以通过首先使用正确的 Jet OLEDB:Database Locking Mode 设置通过 OLEDB 连接打开数据库来关闭它，这将允许您将数据库设置为页面级锁定。随后的连接、DAO 或其他方式将遵守此属性，因此您可以使用 DAO 进行快速更新等。

This will then allow DAO to revert to the usual 8X performance compared to executing SQL statements.

与执行 SQL 语句相比，这将允许 DAO 恢复到通常的 8 倍性能。

Here are a couple of links pointing to the issue:

以下是指向该问题的几个链接：

Does ACEDAO support row level locking?

ACEDAO 是否支持行级锁定？

http://www.access-programmers.co.uk/forums/showthread.php?t=47040

http://www.access-programmers.co.uk/forums/showthread.php?t=47040

MS KB article, including code of setting locking mode with ADO, then using DAO on that DB - http://support.microsoft.com/?id=306435

MS KB 文章，包括使用 ADO 设置锁定模式的代码，然后在该数据库上使用 DAO - http://support.microsoft.com/?id=306435