graga
asked on
SQL Syntax required for ALTER TABLE
Hi Experts,
I need to change existing table using ALTER TABLE query.
I want to add few new fields to the table and I got stucked on onr type, NUMERIC.
The field I want to create is has following parameters:
Data Type: NUMBER
Field Size: SINGLE
Format: Fixed
Decimal Places: 2
I have tried all known to me syntaxes without success and MS Access help is useless.
I need to change existing table using ALTER TABLE query.
I want to add few new fields to the table and I got stucked on onr type, NUMERIC.
The field I want to create is has following parameters:
Data Type: NUMBER
Field Size: SINGLE
Format: Fixed
Decimal Places: 2
I have tried all known to me syntaxes without success and MS Access help is useless.
"You just can't specify field properties using some SQL-statement"
Huh? SQL has been around longer than DAO and you can.
ALTER TABLE myTable ADD COLUMN New_Field SINGLE
FROM HELP FILE:
-------------------------- ---------- ---------- ---------- -
Syntax
ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }
The ALTER TABLE statement has these parts:
Part Description
table The name of the table to be altered.
field The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table.
type The data type of field.
size The field size in characters (Text and Binary fields only).
index The index for field. For more information on how to construct this index see CONSTRAINT Clause.
multifieldindex The definition of a multiple-field index to be added to table. For more information on how to construct this index see CONSTRAINT Clause.
indexname The name of the multiple-field index to be removed.
Remarks
Using the ALTER TABLE statement you can alter an existing table in several ways. You can:
Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
You can also define an index on that field. For more information on single-field indexes see CONSTRAINT Clause.
If you specify NOT NULL for a field then new records are required to have valid data in that field.
Use ALTER COLUMN to change the data type of an existing field. You specify the field name, the new data type, and an optional size for Text and Binary fields. For example, the following statement changes the data type of a field in the Employees table called ZipCode (originally defined as Integer) to a 10-character Text field:
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
Use ADD CONSTRAINT to add a multiple-field index. For more information on multiple-field indexes see CONSTRAINT Clause.
Use DROP COLUMN to delete a field. You specify only the name of the field.
Use DROP CONSTRAINT to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
Notes
You cannot add or delete more than one field or index at a time.
You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.
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. However, you can apply the NOT NULL restriction only once to a field. Attempting to apply this restriction more than once restuls in a run-time error.
-------------------------- ---------- ---------- ---------- -
GL, K
Huh? SQL has been around longer than DAO and you can.
ALTER TABLE myTable ADD COLUMN New_Field SINGLE
FROM HELP FILE:
--------------------------
Syntax
ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }
The ALTER TABLE statement has these parts:
Part Description
table The name of the table to be altered.
field The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table.
type The data type of field.
size The field size in characters (Text and Binary fields only).
index The index for field. For more information on how to construct this index see CONSTRAINT Clause.
multifieldindex The definition of a multiple-field index to be added to table. For more information on how to construct this index see CONSTRAINT Clause.
indexname The name of the multiple-field index to be removed.
Remarks
Using the ALTER TABLE statement you can alter an existing table in several ways. You can:
Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
You can also define an index on that field. For more information on single-field indexes see CONSTRAINT Clause.
If you specify NOT NULL for a field then new records are required to have valid data in that field.
Use ALTER COLUMN to change the data type of an existing field. You specify the field name, the new data type, and an optional size for Text and Binary fields. For example, the following statement changes the data type of a field in the Employees table called ZipCode (originally defined as Integer) to a 10-character Text field:
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
Use ADD CONSTRAINT to add a multiple-field index. For more information on multiple-field indexes see CONSTRAINT Clause.
Use DROP COLUMN to delete a field. You specify only the name of the field.
Use DROP CONSTRAINT to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.
--------------------------
Notes
You cannot add or delete more than one field or index at a time.
You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.
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. However, you can apply the NOT NULL restriction only once to a field. Attempting to apply this restriction more than once restuls in a run-time error.
--------------------------
GL, K
This line will not change the Decimal Places:
fld.Properties.Append fld.CreateProperty("Decima lPlaces", dbInteger, 2)
zuijdhoek:
Did you get it to create the Decimal Places?
Thanks!
Joe
fld.Properties.Append fld.CreateProperty("Decima
zuijdhoek:
Did you get it to create the Decimal Places?
Thanks!
Joe
If you set the field type to single, you can't specify the number of decimals for storage... only for display of data (the format).
If you want the ability to set decimals for storage, then you need to use the Decimal datatype, new in Access 9.0 (2000). Its uses are limited in that version though, and you may not be able to do certain things with the field, like you can with a single.
K
If you want the ability to set decimals for storage, then you need to use the Decimal datatype, new in Access 9.0 (2000). Its uses are limited in that version though, and you may not be able to do certain things with the field, like you can with a single.
K
ASKER
I agree with KMAN,
Looks like SINGLE, as well as NUMERIC don't allow to specity decimals.
KMAN, do you have the syntax for adding DECIMAL? I'm running 2000 but can't get it to work.
The following fails:
ALTER TABLE MyTable ADD NewField DECIMAL
Looks like SINGLE, as well as NUMERIC don't allow to specity decimals.
KMAN, do you have the syntax for adding DECIMAL? I'm running 2000 but can't get it to work.
The following fails:
ALTER TABLE MyTable ADD NewField DECIMAL
Have you tried something like?:
ALTER TABLE MyTable ADD NewField DECIMAL(16,2)
I'll have a go over here on the syntax for decimal...
ALTER TABLE MyTable ADD NewField DECIMAL(16,2)
I'll have a go over here on the syntax for decimal...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, looks like there is no way to do it.
I think I need to use CURRENCY to at least get close to what I want.
I think I need to use CURRENCY to at least get close to what I want.
You just can't specify field properties using some SQL-statement. Assuming you're using DAO you can add fields to a table and specify it's properties. This is an example of how to do it:
Sub AlterTable()
On Error GoTo ErrHandling
Dim tdf As TableDef
Dim db As Database
Dim fld As Field
Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
Set fld = tdf.CreateField("MyField",
tdf.Fields.Append fld 'append field first!
'these properties don't belong to the default collection
'of field-properties so have to be created first
fld.Properties.Append fld.CreateProperty("Format
fld.Properties.Append fld.CreateProperty("Decima
ExitHere:
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandling:
Select Case Err.Number
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ExitHere
End Sub
Hopefully this makes sense to you,
Mark