For one wacky reason or another, I'm writing a COM component for use in an ASP page that dynamically generates Microsoft Access tables based on various criteria.
I've just spent an hour racking my brain and checking Google for the correct syntax to create an Autonumber field using a SQL CREATE TABLE statement and now I've found it, I figured it would be sensible to share it.
Intuitively, you'd think the syntax would be something like CREATE TABLE MyTable (MyID autonumber primary key not null) but no...the keyword is "counter" not "autonumber". So:
this is covered in microsoft's online documentation, although i grant you that it is never easy to find what you're looking for on microsoft's site, simply because of the sheer volume of results you get in a search on words like "access" and "DDL"
But if you do a search in Access on the word "type", you'll find "data type comparison" (in french it is "Comparaison de types de données"). Here, ALL data types is included including the VBA keyword and DAO constant. You can see that COUNTER have a synonym, AUTOINCREMENT.
In access, AutoIncrement can also be "random", but it is not in that page (someone know what it is?)