By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,220 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,220 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!
Feb 27 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRANzo4echKqOuFEgEQKjAACgkL78xz/ZAwnxL4FcWE90iM8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----
Feb 27 '06 #2

P: n/a
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" <me@privacy.com> wrote in message
news:ys***************@newsread3.news.pas.earthlin k.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRANzo4echKqOuFEgEQKjAACgkL78xz/ZAwnxL4FcWE90iM8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----

Feb 27 '06 #3

P: n/a
-----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_column

ALTER TABLE table_name DROP COLUMN autonumber_column

ALTER TABLE table_name ADD autonumber_column_name LONG

UPDATE table_name
SET autonumber_column_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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRAOF/oechKqOuFEgEQLm6QCfScid23s1NjOP4+2ZdKL01Lvi/hsAoLOa
PEpGQB/8EhzgGar+snRc3UMz
=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" <me@privacy.com> wrote in message
news:ys***************@newsread3.news.pas.earthlin k.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRANzo4echKqOuFEgEQKjAACgkL78xz/ZAwnxL4FcWE90iM8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----


Feb 27 '06 #4

P: n/a
Thanks!

"MGFoster" <me@privacy.com> wrote in message
news:jC***************@newsread3.news.pas.earthlin k.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_column

ALTER TABLE table_name DROP COLUMN autonumber_column

ALTER TABLE table_name ADD autonumber_column_name LONG

UPDATE table_name
SET autonumber_column_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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRAOF/oechKqOuFEgEQLm6QCfScid23s1NjOP4+2ZdKL01Lvi/hsAoLOa
PEpGQB/8EhzgGar+snRc3UMz
=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" <me@privacy.com> wrote in message
news:ys***************@newsread3.news.pas.earthlin k.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRANzo4echKqOuFEgEQKjAACgkL78xz/ZAwnxL4FcWE90iM8/OjUAnRAP
hyxx4rYFXpISBx/ENjgQ6qgo
=7rPR
-----END PGP SIGNATURE-----



Feb 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.