ALTER TABLE/ALTER COLUMN problem with MS Access 2K 
Author Message
 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
 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
 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
 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
 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
 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
 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
 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
 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
 
 [ 9 post ] 

 Relevant Pages 

1. Alter table Alter column problem.

2. Alter table alter column syntax

3. ALTER TABLE ALTER COLUMN to set Default

4. Alter table alter column text

5. Importing MS Access Tables/ALTER COLUMNS

6. Alter table alter column

7. Alter table alter column

8. Alter Table - Alter Column - Set Default ?'s

9. ALTER TABLE ... ALTER COLUMN IDENTITY

10. ALTER TABLE ALTER COLUMN

11. Errors With ALTER TABLE/ALTER COLUMN

12. ALTER TABLE ALTER COLUMN


 
Powered by phpBB® Forum Software