Help with ALTER TABLE statement

[Robert Valentine]

Group:

I have the following statement in a module that adds a column to an existing
table. The column is titled ID2 and it is formatted to accept numbers.

BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

The problem is that I need to set the following parameters: field size to
Long Integer, format to General Number, Required to No, and Indexed to
Yes(Duplicates OK). How do I do this through code?

Thanks,
BobV

 

[Allen Browne]

Replace:
Number
with:
LONG
to get the Long Integer size.

Required will default to No.

The field's Format property cannot be set with a DDL query statement. You
must use DAO code to set the Format of the Field in the TableDef. However,
since you only want General Number, and the Long cannot contain fractional
values anyway, this seems irrelevant (unless it was just an example.)

To set the Indexed property, execute another DDL query statement, using
CREATE INDEX.

 

[Marshall Barton]

Replace:
Number
with:
LONG
to get the Long Integer size.

Required will default to No.

The field's Format property cannot be set with a DDL query statement. You
must use DAO code to set the Format of the Field in the TableDef. However,
since you only want General Number, and the Long cannot contain fractional
values anyway, this seems irrelevant (unless it was just an example.)

To set the Indexed property, execute another DDL query statement, using
CREATE INDEX.

Allen,
Is there a reason for using a Create Index query, instead of
a CONSTRAINT clause in the ALTER TABLE query.

Robert,
As for the Format property, I think it's irrelevant in any
situation. All it does is format the table's sheet view,
which should never be displayed to users, so who cares what
it looks like.

 

[Allen Browne]

Marsh, I don't use DDL much, so I'm not sure how to add a CONSTRAINT that
will index the field "Duplicates Ok." Is that a constraint?

 

[Marshall Barton]

Marsh, I don't use DDL much, so I'm not sure how to add a CONSTRAINT that
will index the field "Duplicates Ok." Is that a constraint?

Well I don't use it all that much either Allen, so I have to
look it up in Help every time I do ;-)

AFAIK, an allow duplicates index/constraint just doesn't
specify the UNIQUE option.

The way I undestand it Create Index is not quite a subset of
a Constraint clause. A constraint can also specify a
relationship with or without the Cascade options, but there
may be a difference that I am not aware of - thus my earlier
question.

Re our long standing issue with invisible, duplicate
indexes, I did dig into the relationship part of a
Constraint clause once in hopes of using an existing index
instead of having the relationship create a duplicate index,
but no, a relationship can not be specified unless a new
index is also specified :-(

Checking Help once again, I see that I will have to dig up
those old experiments and try again using A02/03 to see what
the new NO INDEX option does.

 

[Robert Valentine]

Marshall:

What would the ALTER TABLE statement look like using CONSTRAINT to set an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

Any help will be greatly appreciated.
BobV

 

[Marshall Barton]

What would the ALTER TABLE statement look like using CONSTRAINT to set an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[BobV]

Marshall:

Thanks for the help. I really appreciate it.

I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled "ndx"
and no space between ndx and ID2?

Thanks again,
BobV

What would the ALTER TABLE statement look like using CONSTRAINT to set an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[Marshall Barton]

It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.
--
Marsh
MVP [MS Access]

I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled "ndx"
and no space between ndx and ID2?

What would the ALTER TABLE statement look like using CONSTRAINT to set an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[BobV]

Thank you Marsh for your help. Again, I really appreciate it.

BobV

It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.
--
Marsh
MVP [MS Access]

I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled
"ndx"
and no space between ndx and ID2?

Robert Valentine wrote:
What would the ALTER TABLE statement look like using CONSTRAINT to set
an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to
create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"

"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[BobV]

I tried the suggested Alter Table statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] " ADD COLUMN ID2 Number
CONSTRAINT ndxID2"

However, I could not get it to work when the index constraint syntax is
used. Has anyone been able to get the statement to work?

Thanks,
BobV

Thank you Marsh for your help. Again, I really appreciate it.

BobV

It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.
--
Marsh
MVP [MS Access]

I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled
"ndx"
and no space between ndx and ID2?


Robert Valentine wrote:
What would the ALTER TABLE statement look like using CONSTRAINT to set
an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to
create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"


"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[Douglas J. Steele]

I've found that sometimes you need to use ADO to run the DDL statements,
rather than DAO as you're using.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried the suggested Alter Table statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] " ADD COLUMN ID2 Number
CONSTRAINT ndxID2"

However, I could not get it to work when the index constraint syntax is
used. Has anyone been able to get the statement to work?

Thanks,
BobV

Thank you Marsh for your help. Again, I really appreciate it.

BobV

It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.
--
Marsh
MVP [MS Access]


BobV wrote:
I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled
"ndx"
and no space between ndx and ID2?


Robert Valentine wrote:
What would the ALTER TABLE statement look like using CONSTRAINT to set
an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to
create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"


"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[Marshall Barton]

Excuse me while I wipe the egg off my face :-(

It seems that my example code was inadequate for your
situation. A CONSTRAINT clause apparently requires at least
one option to be used and you want the one combination that
doesn't use any options.

Go back to Allen's suggestion and use the CREATE INDEX
query.

Excuse me, I have to go post an apology to Allen for butting
in and making a mess of your thread.
--
Marsh
MVP [MS Access]

I tried the suggested Alter Table statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] " ADD COLUMN ID2 Number
CONSTRAINT ndxID2"

However, I could not get it to work when the index constraint syntax is
used. Has anyone been able to get the statement to work?

Thank you Marsh for your help. Again, I really appreciate it.

It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.


BobV wrote:
I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled
"ndx"
and no space between ndx and ID2?


Robert Valentine wrote:
What would the ALTER TABLE statement look like using CONSTRAINT to set
an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to
create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"


"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language

 

[Marshall Barton]

Marsh, I don't use DDL much, so I'm not sure how to add a CONSTRAINT that
will index the field "Duplicates Ok." Is that a constraint?

Allen,
Well, I was at least partially wrong about this whole issue.
While it's true that leaving out UNIQUE will deal with the
Duplicates OK issue, the NOT NULL option must then be
specified. This does not meet Bob's needs so that's the
reason he needs to use the CREATE INDEX statement.

There are more differences between CONSTRAINT and CREATE
INDEX than I had realized.

Sorry about all the confusion I brought to the thread.

 

[BobV]

Marshall:

Thank you for all of your time. I will look into the Create Index statement.

BobV

Excuse me while I wipe the egg off my face :-(

It seems that my example code was inadequate for your
situation. A CONSTRAINT clause apparently requires at least
one option to be used and you want the one combination that
doesn't use any options.

Go back to Allen's suggestion and use the CREATE INDEX
query.

Excuse me, I have to go post an apology to Allen for butting
in and making a mess of your thread.
--
Marsh
MVP [MS Access]

I tried the suggested Alter Table statement:

BackDb.Execute "ALTER TABLE [" & CoName & "] " ADD COLUMN ID2 Number
CONSTRAINT ndxID2"

However, I could not get it to work when the index constraint syntax is
used. Has anyone been able to get the statement to work?

Thank you Marsh for your help. Again, I really appreciate it.


It doesn't matter what that is, it's just the name of the
index and you are highly unlikly to ever use that name
anywhere. I just made up a name that would not conflist
with any other indexes for the table.


BobV wrote:
I want to make sure of one thing. Is the last part of the statement
"CONSTRAINT ndxID2"? In other words, is it "ndxID2" with index spelled
"ndx"
and no space between ndx and ID2?


Robert Valentine wrote:
What would the ALTER TABLE statement look like using CONSTRAINT to
set
an
index on the added column labeled ID2? I want the index to be
"Yes(Duplicates OK)". Do I also have to use the CREATE statement to
create
the index?

Here is the original statement:
BackDb.Execute "ALTER TABLE [" & CoName & "] ADD COLUMN ID2 Number;"


"Marshall Barton" wrote
It hink this is all you need:

BackDb.Execute "ALTER TABLE [" & CoName & "] " & _
"ADD COLUMN ID2 Number CONSTRAINT ndxID2"

You can find all this stuff in Access (not VBA) Help -
Contents - MS Jet SQL Reference - Data Definition Language