Field types available for use with "CREATE TABLE" in Microsoft Access

Question

I have the displeasure of generating table creation scripts for Microsoft Access. I have not yet found any documentation describing what the syntax is for the various types. I have found the documentation for the Create Table statement in Access but there is little mention of the types that can be used. For example:

CREATE TABLE Foo (MyIdField *FIELDTYPE*)

Where FIELDTYPE is one of...? Through trial and error I've found a few like INTEGER, BYTE, TEXT, SINGLE but I would really like to find a page that documents all to make sure I'm using the right ones.

Answer 1

I've found the table in the link below pretty useful:

http://allenbrowne.com/ser-49.html

It lists what Access's Gui calls each data type, the DDL name, DAO name and ADO name (they are all different...).

Answer 2

Some of the best documentation from Microsoft on the topic of SQL Data Definition Language (SQL DDL) for ACE/Jet can be found here:

Intermediate Microsoft Jet SQL for Access 2000

Of particular interest are the synonyms, which are important for writing portable SQL code.

One thing to note is that the Jet 4.0 version of the SQL DDL syntax requires the interface to be in ANSI-92 Query Mode; the article refers to ADO because ADO always uses ANSI-92 Query Mode. The default option for the MS Access interface is ANSI-89 Query Mode, however from Access2003 onwards the UI can be put into ANSI-92 Query Mode. All versions of DAO use ANSI-89 Query Mode. I'm not sure whether SQL DDL syntax was extended for ACE for Access2007.

For more details about query modes, see About ANSI SQL query mode (MDB)