Microsoft Access 2003 是否包含集或多集？

发布于 2024-08-10 10:13:52 字数 354 浏览 4 评论 0原文

我试图确认或否认是否可以将 MS Access 2003 中的表列定义为集合。看来此已在 Office 2007 中实现 - 您可以定义一个列以在查询/查找中具有“多选列表”，但此功能似乎是新的 access 2007 文件格式所独有的据我所知。

换句话说，MS Access 2003 是否具有与 SQL 语句等效的功能：

CREATE TABLE mytable (foo VARCHAR(10), bar VARCHAR(5) MULTISET);

或者是否有一个巧妙的解决方法来实现类似的功能？我接受提供有关 Access 2003 中任何集合构造函数的信息的答案。

原文

I'm trying to confirm or deny whether you can define a table column in MS Access 2003 as a set. It seems this is implemented in Office 2007 - you can define a column to have a 'multi-select list' in the query/lookup, but this feature appears to be unique to the new access 2007 file format as far as I can determine.

Worded another way, does MS Access 2003 have the equivalent to the SQL statement:

CREATE TABLE mytable (foo VARCHAR(10), bar VARCHAR(5) MULTISET);

Or is there a clever workaround to achieve something similar? I would accept an answer providing information on any collection constructors in Access 2003.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

成熟的代价 2024-08-17 10:13:52

您指的是 Access 数据库引擎的多值数据类型吗？如果是这样，那么是的，这些对于 ACE (2007) 版本的引擎来说是新的，并且在作为 Access2003 版本的引擎的 Jet 4.0 中不可用。

FWIW我在Access2007中使用ANSI-92查询模式（OLE DB，引擎类型= 5）尝试了您的SQL，并且无法识别MULTISET关键字。

请注意，您可能不需要也不想要多值类型。一项特别的批评是 Access 数据库 SQL DML 表达式服务尚未进行更改以考虑多值类型。另请参阅这篇文章被视为有害的多值数据类型：

Suraj [Poozhiyil，MS Access
项目经理]我同意
衷心希望开发商不
需要使用多值字段。
懂数据库的人
已经有好办法了
实施多对多
关系，并且不会获得任何利益
来自多值字段。
所以，我明确而明确的建议
开发者不要使用多值
字段。他们没有什么可以提供给我们的
除了潜在的疼痛。

更新：

MULTISET 是正式的新数据类型
从 SQL:2003 开始，所以我
猜测部分添加原因
Access 2007 中的它是完全
符合SQL标准

这很有趣。访问团队没有表现出添加符合任何 SQL 标准的 SQL 语法的兴趣。

[当 SQL Server 团队修改 Jet 的 4.0 版本时，他们希望获得 SQL-92 合规性，但 Windows 团队阻止了这样做，因为 Windows 团队的组件依赖于一些仍然不合规的功能……但那是另一回事了。访问团队拥有自己的代码库私人人员，因此他们没有这样的借口...除非 SharePoint 团队现在拥有不当影响力？我离题了...]

考虑一下有关 SQL2003 标准的文档中的这段引用：

MULTISET 类型的值可以是
通过枚举创建
单个元素或通过提供
通过查询得到的元素
表达;例如，
多集[1,2,3,4]
或
多集(
选择成绩
来自课程
<代码>）
...相反，多重集值可以是
用作 FROM 中的表引用
使用 UNNEST 运算符的子句。

访问团队尚未向 ACE SQL DML 语法添加任何新表达式或任何运算符。所以，不，这与 SQL 标准无关，而与 SharePoint 有关。

大卫·W·芬顿：不，[支持
多值类型]被添加到
ACCDB 格式（不是 ACE，如
@onedaywhen 说...)

请考虑来自访问团队的引用自己的博客：

我们添加到新版本中的主要功能
访问引擎支持“复杂
数据”。

这绝对是引擎功能！

Are you referring to the Access Database Engine's multivalued data types? If so then yes, these are new to the ACE (2007) version of the engine and are not available in Jet 4.0 being Access2003's version of the engine.

FWIW I tried your SQL in Access2007 using ANSI-92 Query Mode (OLE DB, engine type = 5) and the MULTISET keyword wasn't recognized.

Note you may not need nor want multivalued types. One particular criticism is that Access Database SQL DML expressions service hasn't been altered to take account of multivalued types. Also, see this article Multivalued datatypes considered harmful:

both Suraj [Poozhiyil, the MS Access
Program Manager] and I agree
wholeheartedly that developers do not
need to use multi-valued fields.
People who understand databases
already have a good way of
implementing many to many
relationships and will gain no benefit
from multi-valued fields.
So, my clear and certain advice to
developers is not to use multi-valued
fields. They have nothing to offer us
except potential pain.

UPDATE:

MULTISET is a new datatype officially
beginning with SQL:2003 so I'm
guessing part of the reason for adding
it in Access 2007 is to be fully
compliant with the SQL standard

That's almost amusing. The Access Team have shown no interest in adding SQL syntax that is compliant with any SQL Standard.

[When the SQL Server team were modifying Jet for its 4.0 release they wanted to attain SQL-92 compliance but were prevented from doing so by the Windows team whose components were reliant on some features remaining non-compliant... but that's another story. The Access Team have their own private folk of the code base so they've no such excuse... unless the SharePoint Team now has undue influence? I digress...]

Consider this quote from the document about the SQL2003 Standard:

Values of a MULTISET type can be
created either by enumerating the
individual elements or by supplying
the elements through a query
expression; e.g.,
MULTISET[1, 2, 3, 4]
or
MULTISET(
SELECT grades
FROM courses
)
...Conversely, a multiset value can be
used as a table reference in the FROM
clause using the UNNEST operator.

The Access Team has not added any new expressions nor any operators to the ACE SQL DML syntax. So, no, this has nothing to do with SQL Standards and everything to do with SharePoint.