modifiy field type by vba

[Joseph Atie]

Im trying to modify a field type in a table

i understand that you can do this with sql

using something such:

ALTER TABLE catalogue_mat ALTER COLUMN cat_code autonumber;

my problem is i clearly dont have the correct syntax as running this sql
query through docmd.runsql()

returns the error autonumber is not a valid field in catalogue_mat

help please

 

[Alex Dybenko]

hi,
try to use execute method:

currentdb.execute "ALTER TABLE catalogue_mat ALTER COLUMN cat_code
autonumber", dbfailonerror

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

 

[Allen Browne]

Here's an example:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

 

[Joseph Atie]

Hi Allen

I tried the code you posted

specificly "ALTER TABLE MyTable ALTER COLUMN ID COUNTER (1000,1);"

so i ended up with the sql call

"ALTER TABLE cataloge_mat ALTER COLUMN cat_code COUNTER (100000,1);"

which still comes up with the error invalid field data type

from the link you gave me i believe it should work, am i doing something
wrong here?

I know i can manually change from a number field to an autonumber on an
access table, is there a reason this shouldnt work in vba?

 

[Douglas J. Steele]

Is there data in the table? You cannot change a field to AutoNumber if there
is.

 

[Allen Browne]

Good call, Doug.

If you are trying to reset the seed of an autonumber:
http://allenbrowne.com/func-ADOX.html#ResetSeed

If you are trying to change a number field into an autonumber with data in
the table, create a new (blank) table with the autonumber, and then use an
Append query to populate it with the data from your existing table.

 

[Joseph Atie]

thanks for your help,

you are correct the column was technically empty, but by deleting and then
recreating it seems to work fine.