Does Microsoft Access 2003 contain sets or multisets?

Question

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.

Answer 1

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.

David W. Fenton: No, [support for multivalued types] was added in the ACCDB format (not the ACE, as @onedaywhen says...)

Consider this quote from the Access Team's own blog:

The primary feature we added to the new Access engine is support for “complex data”.

It is definitely an engine feature!