modifiy field type by vba

  • Thread starter Thread starter Joseph Atie
  • Start date Start date
J

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
 
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?
 
Is there data in the table? You cannot change a field to AutoNumber if there
is.
 
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.
 
thanks for your help,

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