How to create non-unique index with DDL?

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.

On Jan 29 2004, 11:48 am, "deko" <dje422@hotmail.com> wrote in
news:GPaSb.7492$Mv1.5140@newssvr29.news.prodigy.co m:
[color=blue]
> 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?[/color]

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.
[color=blue]
> Or do I need to run a separate statement to add the non-unique index?
>
> CREATE INDEX idxTxDate ON tblTx03 ( TxDate ASC );[/color]

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

--
(remove a 9 to reply by email)

10-4

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

"Dimitri Furman" <dfurman@cloud99.net> wrote in message
news:Xns947FD0799180Ddfurmancloud99@127.0.0.1...[color=blue]
> On Jan 29 2004, 11:48 am, "deko" <dje422@hotmail.com> wrote in
> news:GPaSb.7492$Mv1.5140@newssvr29.news.prodigy.co m:
>[color=green]
> > 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?[/color]
>
> 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.
>[color=green]
> > Or do I need to run a separate statement to add the non-unique index?
> >
> > CREATE INDEX idxTxDate ON tblTx03 ( TxDate ASC );[/color]
>
> This will do it. You may want to wrap both statements in a transaction.
>
> --
> (remove a 9 to reply by email)[/color]