ALTER TABLE/ALTER COLUMN problem with MS Access 2K
| Author |
Message |
|
[codetroll
#1 / 9
|
 ALTER TABLE/ALTER COLUMN problem with MS Access 2K Could someone, please, tell me why this SQL command will not run in MS
Access 2000:
ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2)
Table and field exist and I want to change field type from
Number (Double, Auto-precision) to Number (Double, 2 decimal places
precision).
MS Access reports syntax error at first bracket, while this works perfectly
on SQL Server!
Thank you in advance!
--
{[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 14:53:27 GMT |
|
 |
|
Henry Crave
#2 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K AFAIK you cannot define the Precision of an Access/Jet Number Field.
--
Henry Craven
---------------
Quote: > Could someone, please, tell me why this SQL command will not run in MS > Access 2000: > ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2) > Table and field exist and I want to change field type from
> Number (Double, Auto-precision) to Number (Double, 2 decimal places
> precision).
> MS Access reports syntax error at first bracket, while this works
perfectly
> on SQL Server!
> Thank you in advance!
> --
> {[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 16:11:47 GMT |
|
|
|
[codetroll
#3 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K It's possible in Design mode by setting "Decimal places" to a value
(different from Auto).
It's logical that this would be possible through SQL command too then ... at
least to me.
Quote: > AFAIK you cannot define the Precision of an Access/Jet Number Field. > --
> Henry Craven
> ---------------
> > Could someone, please, tell me why this SQL command will not run in MS
> > Access 2000:
> > ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2)
> > Table and field exist and I want to change field type from
> > Number (Double, Auto-precision) to Number (Double, 2 decimal places
> > precision).
> > MS Access reports syntax error at first bracket, while this works
> perfectly
> > on SQL Server!
> > Thank you in advance!
> > --
> > {[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 16:19:14 GMT |
|
|
|
Henry Crave
#4 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K I think you'll find that that refers to the Display/Formatting,
and not the stored value.
Hmm ...Checks Help:
The DecimalPlaces property affects only the number of decimal places
that display, not how many decimal places are stored.
Bingo.
--
Henry Craven
---------------
Quote: > It's possible in Design mode by setting "Decimal places" to a value > (different from Auto). > It's logical that this would be possible through SQL command too then ... at > least to me.
> > AFAIK you cannot define the Precision of an Access/Jet Number Field.
> > --
> > Henry Craven
> > ---------------
|
| Tue, 25 May 2004 16:34:59 GMT |
|
|
|
[codetroll
#5 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K Hm... you're right!
Then I just have to find another solution.
Thanks anyway.
Quote: > I think you'll find that that refers to the Display/Formatting, > and not the stored value. > Hmm ...Checks Help:
> The DecimalPlaces property affects only the number of decimal places
> that display, not how many decimal places are stored.
> Bingo.
> --
> Henry Craven
> ---------------
> > It's possible in Design mode by setting "Decimal places" to a value
> > (different from Auto).
> > It's logical that this would be possible through SQL command too then
...
> at
> > least to me.
> > > AFAIK you cannot define the Precision of an Access/Jet Number Field.
> > > --
> > > Henry Craven
> > > ---------------
|
| Tue, 25 May 2004 16:58:50 GMT |
|
|
|
Danny J. Lesandrin
#6 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K I was just reading the latest Developer's Handbook last night and I ran
across a comment about using the ALTER statement. It said that you cannot
change an existing column ... at least not the datatype. You can add and
drop columns, but not change existing.
It's been a while since I worked with Oracle, but it seems to me that the
same is true there. If you want to change columns, you need to create a
new table, append all the records and then drop the old table. Seems like
a lot of work, but I think that's the way we did it.
--
Danny J. Lesandrini
http://datafast.cjb.net
Quote: > Could someone, please, tell me why this SQL command will not run in MS > Access 2000: > ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2) > Table and field exist and I want to change field type from
> Number (Double, Auto-precision) to Number (Double, 2 decimal places
> precision).
> MS Access reports syntax error at first bracket, while this works perfectly
> on SQL Server!
> Thank you in advance!
> --
> {[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 21:29:41 GMT |
|
|
|
[codetroll
#7 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K Well ... this is probably not 100% correct.
I had the field as Number (LongInt) and I changed it to Double with this
command.
Didn't try changing from Text to Double - that will probably fail.
--
{[nikica]coding*4[$||:)]}
Quote: > I was just reading the latest Developer's Handbook last night and I ran > across a comment about using the ALTER statement. It said that you cannot > change an existing column ... at least not the datatype. You can add and > drop columns, but not change existing. > It's been a while since I worked with Oracle, but it seems to me that the
> same is true there. If you want to change columns, you need to create a
> new table, append all the records and then drop the old table. Seems like
> a lot of work, but I think that's the way we did it.
> --
> Danny J. Lesandrini
> http://datafast.cjb.net
> > Could someone, please, tell me why this SQL command will not run in MS
> > Access 2000:
> > ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2)
> > Table and field exist and I want to change field type from
> > Number (Double, Auto-precision) to Number (Double, 2 decimal places
> > precision).
> > MS Access reports syntax error at first bracket, while this works
perfectly
> > on SQL Server!
> > Thank you in advance!
> > --
> > {[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 21:52:08 GMT |
|
|
|
Danny J. Lesandrin
#8 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K Just reporting what I read, and that for Access XP. I went back to check
it just now, and sure enough, Getz & Company says ...
With [ALTER TABLE] you can add a new column or constraing or delete a
column or constraint. (You can't modify the definition of either)
Looking at it again, they didn't comment on ALTER COLUMN, but my tests
in Access 2002 shows that you are correct, and all of these work ...
alter table table1 alter column Client_ID text
alter table table1 alter column Client_ID double
alter table table1 alter column Client_ID datetime
The Client_ID changes from Long Integer, to Text, to Double, to Date
Go figure
--
Danny J. Lesandrini
http://datafast.cjb.net
Quote: > Well ... this is probably not 100% correct. > I had the field as Number (LongInt) and I changed it to Double with this > command. > Didn't try changing from Text to Double - that will probably fail. > --
> {[nikica]coding*4[$||:)]}
> > I was just reading the latest Developer's Handbook last night and I ran
> > across a comment about using the ALTER statement. It said that you cannot
> > change an existing column ... at least not the datatype. You can add and
> > drop columns, but not change existing.
> > It's been a while since I worked with Oracle, but it seems to me that the
> > same is true there. If you want to change columns, you need to create a
> > new table, append all the records and then drop the old table. Seems like
> > a lot of work, but I think that's the way we did it.
> > --
> > Danny J. Lesandrini
> > http://datafast.cjb.net
> > > Could someone, please, tell me why this SQL command will not run in MS
> > > Access 2000:
> > > ALTER TABLE [TableName] ALTER COLUMN [FieldName] DOUBLE (15,2)
> > > Table and field exist and I want to change field type from
> > > Number (Double, Auto-precision) to Number (Double, 2 decimal places
> > > precision).
> > > MS Access reports syntax error at first bracket, while this works
> perfectly
> > > on SQL Server!
> > > Thank you in advance!
> > > --
> > > {[nikica]coding*4[$||:)]}
|
| Tue, 25 May 2004 22:49:47 GMT |
|
|
|
David W. Fent
#9 / 9
|
ALTER TABLE/ALTER COLUMN problem with MS Access 2K
Quote: >It's possible in Design mode by setting "Decimal places" to a
>value (different from Auto).
>It's logical that this would be possible through SQL command too
>then ... at least to me.
You need to realize that there is a distinction between Jet and
Access. The SQL works only on Jet structures, and the decimal
places property is one that is added to the table by Access. That
is, Jet doesn't know about this "custom" property. To manipulate
it, you must use DAO instead, and get to it through the .Properties
collection of the relevant .TableDef.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
|
| Thu, 27 May 2004 04:47:43 GMT |
|
| |
|
