SQL Syntax: ALTER TABLE

[flowcontrol]

What is the correst syntax for ALTER TABLE in MS Access?

I am trying to use the following, but Access 2002 objects to the word MODIFY:

alter table users
modify
(
lastname NOT NULL
)

 

[drctx]

here's a sample from an SQL book i have.

alter table lawyer8 add (
FMName varchar2(15))
;

 

[rdodge]

How are you trying to alter the table exactly? In SQL, you have the UPDATE Action Query Type. The following is what I typed up in a Word Document for such an action query.

UPDATE Statement (Update Query)
Syntax

UPDATE <Table/Query>
SET <Field1> = <Expression1>[, <Field2> = <Expression2>[, …]]
WHERE <Criteria>

This type of query updates records in table(s) based on the <Criteria> provided. The specified fields will be modified for all such records accordingly. For more information on the Where Clause, see Chapter 2.

Example:

Employer evaluate all of it’s employees starting 2 months before the end of the fiscal year. The rating is setup to be similar to what the pay raise will be.

UPDATE tblEmp
SET fldRate = fldRate * (1 + Int(fldRating * 2) / 200)
WHERE fldRating >= 2;

Between the time when the last check for the old fiscal year is completely processed and the start of the first time checks are processed in the new fiscal year, this update query would be ran to update the employee pay rates as expected.

Note, you can have multiple fields updated within the same query, just have to specify each one accordingly and use a comma between each field that’s to be updated.


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[flowcontrol]

Very curious responses!

drctx:

The problem is not with the ADD operation, but with MODIFY. You'll notice that my example is of the modify operation and that the text of my question says that Access doesn't like MODIFY. The ADD operation works fine in Access.

rdodge:

I think you are confusing UPDATE TABLE with ALTER TABLE. 'Update table' is for modifying the data in the table, &quot;alter table' is for changing the table structure or constraints. ALTER TABLE comes from the DDL part of SQL.

Everyone:

When I issue the SQL command in standard SQL-86 syntax:

alter table users
modify
(lastname not null)

Access complains that the syntax is incorrect and highlights the word &quot;modify&quot;. Does anyone know what the correct systax is

 

[glalsop]

Have a look here, bout halfway down the page:

http://support.microsoft.com/default.aspx?scid=kb;en-us;180841&Product=acc2000

-Gary

 

[glalsop]

This addresses your situation more directly. Try ALTER COLUMN. From

http://support.microsoft.com/default.aspx?scid=kb;en-us;275561&Product=acc2000

:

Modifying Existing Tables
The ALTER TABLE syntax has been extended to include the action ALTER COLUMN. Without direct support for altering a field in earlier versions of Microsoft Jet, the only way to change a field's definition was to add a new field, copy the data from the existing field, then drop the original field. Support for ALTER COLUMN simplifies changing field definitions. For example, given the following table definition: CREATE TABLE TableName (FieldName1 INTEGER, FieldName2 CHAR)

The data type of the field FieldName1 can be changed using the ALTER COLUMN syntax as follows: ALTER TABLE TableName ALTER COLUMN FieldName1 CHAR

Additional syntax for the ALTER TABLE: <alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column
action>

<alter column action> ::= <set column default clause> | <drop column
default clause> | <column definition>

<column definition> ::= <column name> <data type> [ <default clause>] [
<column constraint definition> ]

<column constraint definition> ::= [ <constraint name definition> ] |
<unique specification> | <references specification> | <check constraint
definition

As a side note (and in addition) to changing field data types or changing the seed and increment value of an Auto-increment data type, the programmer can also drop fields and constraints.

-Gary

 

[flowcontrol]

THANK YOU!

I had searched and searched MSDN for that information, but to no avail.

To all of you reading this, ALTER TABLE with the MODIFY operation is &quot;not available&quot; in Access. In Jet 4.0, an ALTER COLUMN clause has been introduced to use where you would have used &quot;MODIFY&quot;. According to the BNF, you can only specify one column in each ALTER TABLE statement. Also, if you want to change the column definition, you must always specify the datatype, even if you only want to change the default or the constraint. Read glalsop's reply above or follow the hyperlink to the document for syntax details.

So, modifying my example, this is what works in Access:

alter table users
alter lastname char(50) NULL;

 

[rdodge]

I guess that's where I would have attempted to do it more so via the TableDefs and Fields Collection and Object, rather than try to setup a SQL type statement to do these things.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[flowcontrol]

Yes, I agree with you. Unfortunately, I needed to do this through ODBC. So, I don't have access to any of the objects. I appreciate your taking time to reply. thanks.