Any way to remove the AutoIncr / Autonum attribute using DAO?

[Rico]

Hello,

I'd like to change a field from an Autonum to a Long data type using DAO. I
knwo how to set the attribute, but don't know how to remove it. Any ideas?

Thanks!

[MGFoster]

Rico wrote:

Hello,

I'd like to change a field from an Autonum to a Long data type using DAO. I
knwo how to set the attribute, but don't know how to remove it. Any ideas?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a DDL statement like this in the SQL view of a Query, then run the
query.

ALTER TABLE <the table name> ALTER COLUMN <the autonumber column> LONG

Ex:

ALTER TABLE Accounts ALTER COLUMN account_id LONG

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRANzo4echKq OuFEgEQKjAACgkL 78xz/ZAwnxL4FcWE90iM 8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----

[Rico]

Hi MG,

Thanks, but is there any way to accomplish this in Access97? I suppose I
should have mentioned that. The client only has Access 97 at this point.

Thanks!
"MGFoster" <[e-mail] > wrote in message
news:ys******** *******@newsrea d3.news.pas.ear thlink.net...

Rico wrote:

Hello,

I'd like to change a field from an Autonum to a Long data type using DAO.
I knwo how to set the attribute, but don't know how to remove it. Any
ideas?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a DDL statement like this in the SQL view of a Query, then run the
query.

ALTER TABLE <the table name> ALTER COLUMN <the autonumber column> LONG

Ex:

ALTER TABLE Accounts ALTER COLUMN account_id LONG

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRANzo4echKq OuFEgEQKjAACgkL 78xz/ZAwnxL4FcWE90iM 8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to create a new, temp column in the table. Copy the
contents of the AutoNumber column into the temp column & then delete the
AutoNumber column. To get the old AutoNumber column's name you'll have
to create a new column w/ the old AutoNumber's column name and copy the
temp column's values into the new column w/ the old name. Then delete
the temp column. E.g.:

ALTER TABLE table_name ADD temp_column LONG

UPDATE table_name
SET temp_column = autonumber_colu mn

ALTER TABLE table_name DROP COLUMN autonumber_colu mn

ALTER TABLE table_name ADD autonumber_colu mn_name LONG

UPDATE table_name
SET autonumber_colu mn_name = temp_column

ALTER TABLE table_name DROP COLUMN temp_column

Fun in SQL89.

Each of these statements will have to be run in separate QueryDefs (or
using VBA & the CurrentDB object, in separate Execute methods).
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAOF/oechKqOuFEgEQLm 6QCfScid23s1NjO P4+2ZdKL01Lvi/hsAoLOa
PEpGQB/8EhzgGar+snRc3U Mz
=6A2m
-----END PGP SIGNATURE-----

Rico wrote:

Hi MG,

Thanks, but is there any way to accomplish this in Access97? I suppose I
should have mentioned that. The client only has Access 97 at this point.

Thanks!
"MGFoster" <[e-mail] > wrote in message
news:ys******** *******@newsrea d3.news.pas.ear thlink.net...

Rico wrote:

Hello,

I'd like to change a field from an Autonum to a Long data type using DAO.
I knwo how to set the attribute, but don't know how to remove it. Any
ideas?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a DDL statement like this in the SQL view of a Query, then run the
query.

ALTER TABLE <the table name> ALTER COLUMN <the autonumber column> LONG

Ex:

ALTER TABLE Accounts ALTER COLUMN account_id LONG

--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRANzo4echKq OuFEgEQKjAACgkL 78xz/ZAwnxL4FcWE90iM 8/OjUAnRAP
hyxx4rYFXpISB x/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----

[Rico]

Thanks!

"MGFoster" <[e-mail] > wrote in message
news:jC******** *******@newsrea d3.news.pas.ear thlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to create a new, temp column in the table. Copy the
contents of the AutoNumber column into the temp column & then delete the
AutoNumber column. To get the old AutoNumber column's name you'll have
to create a new column w/ the old AutoNumber's column name and copy the
temp column's values into the new column w/ the old name. Then delete
the temp column. E.g.:

ALTER TABLE table_name ADD temp_column LONG

UPDATE table_name
SET temp_column = autonumber_colu mn

ALTER TABLE table_name DROP COLUMN autonumber_colu mn

ALTER TABLE table_name ADD autonumber_colu mn_name LONG

UPDATE table_name
SET autonumber_colu mn_name = temp_column

ALTER TABLE table_name DROP COLUMN temp_column

Fun in SQL89.

Each of these statements will have to be run in separate QueryDefs (or
using VBA & the CurrentDB object, in separate Execute methods).
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAOF/oechKqOuFEgEQLm 6QCfScid23s1NjO P4+2ZdKL01Lvi/hsAoLOa
PEpGQB/8EhzgGar+snRc3U Mz
=6A2m
-----END PGP SIGNATURE-----

Rico wrote:

Hi MG,

Thanks, but is there any way to accomplish this in Access97? I suppose I
should have mentioned that. The client only has Access 97 at this point.

Thanks!
"MGFoster" <[e-mail] > wrote in message
news:ys******** *******@newsrea d3.news.pas.ear thlink.net...

Rico wrote:

Hello,

I'd like to change a field from an Autonum to a Long data type using
DAO. I knwo how to set the attribute, but don't know how to remove it.
Any ideas?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a DDL statement like this in the SQL view of a Query, then run the
query.

ALTER TABLE <the table name> ALTER COLUMN <the autonumber column> LONG

Ex:

ALTER TABLE Accounts ALTER COLUMN account_id LONG

--
MGFoster:::m gf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRANzo4echKq OuFEgEQKjAACgkL 78xz/ZAwnxL4FcWE90iM 8/OjUAnRAP
hyxx4rYFXpIS Bx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----