Deactivate AutoNumber in vba

[Henry]

does anyone know how to deactivate a fields AutoNumber property in vba?

 

[Alex Dybenko]

Hi,
you need to run these steps: create a new field, number-long, run update
query to copy data from autonumber field to a new one, then delete
autonumber field and rename new field to old name

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Henry]

I tried this, but the table remanes unchanged:

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs(strTableName)

If td(0).Type <> dbLong Then Exit Sub

td(0).Attributes = dbDescending

td.RefreshLink

Set td = Nothing
Set db = Nothing

anyone knows why?

 

[Douglas J. Steele]

I suspect it doesn't work for the same reason that you cannot change the
field's Type property after the field's been added to the Fields collection.

Re-read Alex's suggestion. "create a new field, number-long, run update
query to copy data from autonumber field to a new one, then delete
autonumber field and rename new field to old name"

 

[Henry]

I actualy knew Alex's suggestion, but lookout for a more eficient way to do
it. Also I prefer to keep my index at field(0).

 

[Douglas J. Steele]

What you're doing is a one-time activity. Why do you need to do it
programmatically?

 

[Henry]

This is part of an automated sw-release update. Before the new front-end
starts lots of changes are aplied to the backend.

 

[Tony Toews [MVP]]

This is part of an automated sw-release update. Before the new front-end
starts lots of changes are aplied to the backend.

There is no way to do what you want using VBA code and be simpler.
Yes, when you are in table design view it's very easy and swift. But
not with code.

That said you can indeed insert a field so it's index is 0. The
property is "OrdinalPosition".

Note however that you may get the dreaded -1517 error message when the
user compacts the database and goes into the front end. Relinking
the tables in the FE solves this problem

http://www.granite.ab.ca/access/reservederror1517.htm

BTW I use the Compare'Em utility to compare BE MDBs and create the
necessary VBA code.
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

Although it won't create the code you need to remove and add the
autonumber field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

does anyone know how to deactivate a fields AutoNumber property in vba?

BTW why do you want to do this?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/