Board index » delphi » ALTER TABLE/ALTER COLUMN problem with MS Access 2K

[codetroll]

Delphi Developer
Tue, 25 May 2004 14:53:27 GMT

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[$||:)]}

 
Henry Crave

Delphi Developer
Tue, 25 May 2004 16:11:47 GMT

Re:ALTER TABLE/ALTER COLUMN problem with MS Access 2K


AFAIK you cannot define the Precision of an Access/Jet Number Field.

--
Henry Craven
---------------
H_Cra...@bigpond.com

Quote
"[codetroll]" <nos...@please.org> wrote in message

news:9upp29$6j8a$1@as201.hinet.hr...
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[$||:)]}

[codetroll

Delphi Developer
Tue, 25 May 2004 16:19:14 GMT

Re: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
"Henry Craven" <GospodynNiema...@nyet.net> wrote in message

news:7R_P7.426447$bY5.1780686@news-server.bigpond.net.au...
Quote
> AFAIK you cannot define the Precision of an Access/Jet Number Field.

> --
> Henry Craven
> ---------------
> H_Cra...@bigpond.com

> "[codetroll]" <nos...@please.org> wrote in message
> news:9upp29$6j8a$1@as201.hinet.hr...
> > 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[$||:)]}

Henry Crave

Delphi Developer
Tue, 25 May 2004 16:34:59 GMT

Re: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
---------------
H_Cra...@bigpond.com

Quote
"[codetroll]" <nos...@please.org> wrote in message

news:9uptue$63mp$1@as201.hinet.hr...
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.

> "Henry Craven" <GospodynNiema...@nyet.net> wrote in message
> news:7R_P7.426447$bY5.1780686@news-server.bigpond.net.au...
> > AFAIK you cannot define the Precision of an Access/Jet Number Field.

> > --
> > Henry Craven
> > ---------------

[codetroll

Delphi Developer
Tue, 25 May 2004 16:58:50 GMT

Re:ALTER TABLE/ALTER COLUMN problem with MS Access 2K


Hm... you're right!
Then I just have to find another solution.

Thanks anyway.

Quote
"Henry Craven" <GospodynNiema...@nyet.net> wrote in message

news:Ta%P7.426476$bY5.1781049@news-server.bigpond.net.au...
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
> ---------------
> H_Cra...@bigpond.com

> "[codetroll]" <nos...@please.org> wrote in message
> news:9uptue$63mp$1@as201.hinet.hr...
> > 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.

> > "Henry Craven" <GospodynNiema...@nyet.net> wrote in message
> > news:7R_P7.426447$bY5.1780686@news-server.bigpond.net.au...
> > > AFAIK you cannot define the Precision of an Access/Jet Number Field.

> > > --
> > > Henry Craven
> > > ---------------

Danny J. Lesandrin

Delphi Developer
Tue, 25 May 2004 21:29:41 GMT

Re: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
dlesandr...@hotmail.com
http://datafast.cjb.net

"[codetroll]" <nos...@please.org> wrote ...

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[$||:)]}

[codetroll

Delphi Developer
Tue, 25 May 2004 21:52:08 GMT

Re: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[$||:)]}

"Danny J. Lesandrini" <dlesandr...@hotmail.com> wrote in message
news:9uqg5j$a9e24$1@ID-82595.news.dfncis.de...

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
> dlesandr...@hotmail.com
> http://datafast.cjb.net

> "[codetroll]" <nos...@please.org> wrote ...
> > 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[$||:)]}

Danny J. Lesandrin

Delphi Developer
Tue, 25 May 2004 22:49:47 GMT

Re: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
dlesandr...@hotmail.com
http://datafast.cjb.net

"[codetroll]" <nos...@please.org> wrote ...

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[$||:)]}

> "Danny J. Lesandrini" <dlesandr...@hotmail.com> wrote in message
> news:9uqg5j$a9e24$1@ID-82595.news.dfncis.de...
> > 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
> > dlesandr...@hotmail.com
> > http://datafast.cjb.net

> > "[codetroll]" <nos...@please.org> wrote ...
> > > 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[$||:)]}

David W. Fent

Delphi Developer
Thu, 27 May 2004 04:47:43 GMT

Re:ALTER TABLE/ALTER COLUMN problem with MS Access 2K


nos...@please.org ([codetroll]) wrote in
<9uptue$63m...@as201.hinet.hr>:

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

Other Threads

1. MS Access ALTER TABLE problem - urgent!

2. IB6.0 Bug? ALTER TABLE ALTER Fieldname

3. Alter MS Access Table with ADO

4. Alter Table statement and MS-Access from Code ...

5. Lock Access table prior to ALTER TABLE

6. alter table identity column

7. ALTER TABLE makes VARCHAR column too long

8. ADO, Access 2000, ALTER TABLE

9. ADO, Access 2000, Alter Table and AllowZeroLength

10. Alter Access Table