By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

How to create non-unique index with DDL?

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Nov 12 '05 #2

P: n/a
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)

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.