How to create non-unique index with DDL?

[deko]

I'd like to use DDL (Data Definition Language) rather than VBA code to create a
new table. The below DDL statement creates the table very nicely:

CREATE TABLE tblTx03 (Subject Text (100), Entity_ID Long, TxDate Date, Tx_ID
AutoIncrement Constraint PrimaryKey PRIMARY KEY);

The problem is I want a non-unique index on TxDate - can this be done in the
same statement?

Or do I need to run a separate statement to add the non-unique index?

CREATE INDEX idxTxDate ON tblTx03 ( TxDate ASC );

Thanks in advance.

[Dimitri Furman]

On Jan 29 2004, 11:48 am, "deko" <dj****@hotmail.com> wrote in
news:GP*****************@newssvr29.news.prodigy.co m:

I'd like to use DDL (Data Definition Language) rather than VBA code to
create a new table. The below DDL statement creates the table very
nicely:

CREATE TABLE tblTx03 (Subject Text (100), Entity_ID Long, TxDate
Date, Tx_ID
AutoIncrement Constraint PrimaryKey PRIMARY KEY);

The problem is I want a non-unique index on TxDate - can this be done
in the same statement?
According to Help, only unique or primary key indexes can be specified in
the CREATE TABLE statement in Jet, both 3.5 and 4.0.
Or do I need to run a separate statement to add the non-unique index?

CREATE INDEX idxTxDate ON tblTx03 ( TxDate ASC );

This will do it. You may want to wrap both statements in a transaction.

--
(remove a 9 to reply by email)

[deko]

10-4

I discovered Microsoft Knowledge Base Article 180841, which is also helpful...

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...

On Jan 29 2004, 11:48 am, "deko" <dj****@hotmail.com> wrote in
news:GP*****************@newssvr29.news.prodigy.co m:

I'd like to use DDL (Data Definition Language) rather than VBA code to
create a new table. The below DDL statement creates the table very
nicely:

CREATE TABLE tblTx03 (Subject Text (100), Entity_ID Long, TxDate
Date, Tx_ID
AutoIncrement Constraint PrimaryKey PRIMARY KEY);

The problem is I want a non-unique index on TxDate - can this be done
in the same statement?

According to Help, only unique or primary key indexes can be specified in
the CREATE TABLE statement in Jet, both 3.5 and 4.0.

Or do I need to run a separate statement to add the non-unique index?

CREATE INDEX idxTxDate ON tblTx03 ( TxDate ASC );

This will do it. You may want to wrap both statements in a transaction.

--
(remove a 9 to reply by email)