You can create a table using SQL. You will have to write the SQL for the query yourself as there is no built-in wizard for this.
Here is an example of how to create a table named 'tblSQLMadeMe'.
[NOTE: The data definition language (DDL) CREATE TABLE statement (or any DDL statement, for that matter) only works with Microsoft Access database engine databases.]
SQL Query Example
CREATE TABLE tblSQLMadeMe (
PrimaryKey AUTOINCREMENT,
curFLD CURRENCY,
binFLD BINARY,
intFLD SMALLINT,
lngFLD LONG NOT NULL,
dblFLD DOUBLE,
bytFLD BYTE,
blnFLD LOGICAL,
str50FLD TEXT(50) NOT NULL,
str255FLD TEXT(255),
memoFLD MEMO,
dtFLD DATETIME,
CONSTRAINT UniqueInt UNIQUE (intFLD),
CONSTRAINT PK PRIMARY KEY (PrimaryKey),
CONSTRAINT MultiIndex UNIQUE (PrimaryKey, intFLD, str50FLD, dtFLD)
)
In the example above, I also included lines to create index fields, one of which is the primary key index.
- CONSTRAINT UniqueInt UNIQUE (intFLD)
- Creates an index named "UniqueInt" for intFLD as a unique field
- CONSTRAINT PK PRIMARY KEY (PrimaryKey)
- Creates an index named "PK" for intFLD as the primary key
- CONSTRAINT MultiIndex UNIQUE (PrimaryKey, intFLD, str50FLD, dtFLD)
- Creates an multiple-field index
Here is a list of the data types shown in the example. Most of them are obvious but I thought I should include all the ones shown in the example.
- AUTOINCREMENT - Autonumber
- CURRENCY - Currency
- BINARY - Binary
- SMALLINT - Interger
- LONG - Long
- DOUBLE - Double
- BYTE - Byte
- LOGICAL - Boolean, Yes/No
- TEXT(50) - Text, 50 character length
- TEXT(255) - Text, 255 character length
- MEMO - Memo
- DATETIME - Date/Time
[NOTE: Microsoft Access now has a BigInt data type.]
Links
- CREATE TABLE Statement (Microsoft Access SQL): https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb177893(v%3doffice.12)
- CREATE INDEX Statement (Microsoft Access SQL): https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb177891(v=office.12)