ALTER TABLE syntax?

[manningfan]

Anyone know of an online list that has all the possible syntax for the
Alter Table command? I need to know how to make a field a text,
numeric, currency, double, etc... Also, how to set the length.

Thanks in advance.

 

[Guest]

Not sure if this is what you are looking for . . .

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

 

[Tim Ferguson]

Not sure if this is what you are looking for . . .

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre
f/ts_aa-az_3ied.asp

Transact-SQL is not available for many flavours of Access. This may be
appropriate if the OP is using DAO (watch out for wrapping):

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp?
frame=true#acintsql_exp




Hope that helps


Tim F

 

[Jamie Collins]

This may be
appropriate if the OP is using DAO (watch out for wrapping):

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp?
frame=true#acintsql_exp

It's a good article and one that is even more appropriate if the OP is
using ADO.

I was going to link to this:

http://office.microsoft.com/en-us/assistance/HP010322071033.aspx

....however, it is very confused, IMO.

For example, can anyone explain this to me?

"You can use NOT NULL on a single field or within a named CONSTRAINT
clause that applies to either a single field or to a multiple-field
named CONSTRAINT"

I am aware this is legal syntax:

CREATE TABLE Test5 (
data_col INTEGER
CONSTRAINT data_col__not_null NOT NULL);

Legal but seems to provide no functionality: I can give the NOT NULL
constraint a name but the name does not seem to be retained e.g. in the
ADO's TABLE_CONSTRAINTS schema rowset
(http://msdn.microsoft.com/library/d...s/oledb/htm/oledbtable_constraints_rowset.asp).


However, I can't see how to use this in an ALTER TABLE statement. If
this is the case, why is it mentioned here?

It remind me of this still-unsolved mystery in the same help:

http://office.microsoft.com/en-us/assistance/HP010322201033.aspx

CREATE [TEMPORARY] TABLE

"When a TEMPORARY table is created it is visible only within the
session in which it was created. It is automatically deleted when the
session is terminated. Temporary tables can be accessed by more than
one user."

Jamie.

--

 

[Tim Ferguson]

I was going to link to this:

http://office.microsoft.com/en-us/assistance/HP010322071033.aspx

...however, it is very confused, IMO.

The whole thing is a mess isn't it? This page, in the same volume

http://office.microsoft.com/en-us/assistance/HP010322481033.aspx

has a table of the "13 primary data types" with fourteen rows... and they
seem to have forgotten COUNTER, DOUBLE, CHAR, etc.

For example, can anyone explain this to me?

"You can use NOT NULL on a single field or within a named CONSTRAINT
clause that applies to either a single field or to a multiple-field
named CONSTRAINT"

No... at least, not I!

It remind me of this still-unsolved mystery in the same help:

http://office.microsoft.com/en-us/assistance/HP010322201033.aspx

CREATE [TEMPORARY] TABLE

I get the impression of a documentation team that is working from a
different set of specs from the programming team. It is certainly
possible that temp tables were going to be built into jet but got
withdrawn later. Cf almost anything by Joe Spolsky!

In general, I am really depressed by the direction that Access is taking,
and I can see my future desktop databases being in vb.net/ ado.net/ sql
express, or php/ mySQL. I don't think I'll be testing Access 2007 unless
I hear positive things in the groups here. That means benefits in terms
of data integrity and reliability, not stupid newbie traps like
multivalued fields and autofill.

All the best


Tim F

 

[Jamie Collins]

The whole thing is a mess isn't it? This page, in the same volume

http://office.microsoft.com/en-us/assistance/HP010322481033.aspx

has a table of the "13 primary data types" with fourteen rows... and they
seem to have forgotten COUNTER, DOUBLE, CHAR, etc.

I was going to say that they've used the synonym FLOAT in place of
DOUBLE but then I saw

"DATETIME (See DOUBLE)"

lol!

Similarly, before I could say that COUNTER is not strictly a data type,
more of like a property, I saw:

"Both the seed and the increment can be modified using an ALTER TABLE
statement"

I followed the link to the Equivalent ANSI SQL Data Types and saw more
inconsistencies there.

Yes, this version of the Jet 4.0 SQL Reference is borderline unusable.
Has anyone with more influence than me reported it?

I get the impression of a documentation team that is working from a
different set of specs from the programming team. It is certainly
possible that temp tables were going to be built into jet but got
withdrawn later.

I came to exactly the same conclusions.

In general, I am really depressed by the direction that Access is taking,
and I can see my future desktop databases being in vb.net/ ado.net/ sql
express, or php/ mySQL.

I've always had a soft spot for Jet: as regards data integrity, which
you mentioned, its support for multi-table CHECK constraints is
non-existent in most other SQLs. I think the fact its future
development is now in the hands of the Access team, who have no
interest in fixing any current problems, means Jet is effectively dead
in the water. Jet development has been in limbo for half a decade and
soon people will stop using it. I can only hope this is good news for
Access purists.

Jamie.

--

 

[Tim Ferguson]

I've always had a soft spot for Jet: as regards data integrity, which
you mentioned, its support for multi-table CHECK constraints is
non-existent in most other SQLs.

I came to Access from the file managers like Paradox and dBase... _any_
form on data integrity was non existent in those. I am grateful(!) to MS
for bringing Jet/Access to the popular market but I think it has reached
the end of its road.

I think the fact its future
development is now in the hands of the Access team, who have no
interest in fixing any current problems,

My perspective is that they have an interest in adding marketable
features rather than robustness.

means Jet is effectively dead
in the water.

Sad, but we all have to move on.

All the best


Tim F