I posted a question about this a few weeks back, and stumbling over an
solution yesterday.

The question was how to create an Autonumber field in an Access SQL
statement. All the documentation I'd read to that point (and after)
indicated that it wasn't possible. You first had to define the field as
a LONG in the CREATE TABLE statement, and then use DAO to set a property
for the appropriate Field object.

However, while I was browsing VC's DAO documentation yesterday, I came
across an example of a CREATE TABLE statement that defines fields of
every Access SQL datatype. Turns out there is a barely documented
"COUNTER" data type, which can be used to create an Autonumber field:

CREATE TABLE MyTable(key AS COUNTER,
                     value AS TEXT(50));

If you run that against Access, you'll create a table with an Autonumber
field named 'key'.

(If you're wondering why I've been so persistent about this, and not
just done the DAO properties hack, it's because we use an SQL-based
scripting language to do bulk database manipulations (kind of like T-SQL
in SQL Server, except it runs against Access as well), and the inability
to create tables with Autonumber fields in straight SQL was turning into
a major problem.)