ClOuD_Za
asked on
syntax error to change AUTO_INCREMENT value
Hi
I need to change the column's AUTO_INCREMENT value.
I used this query string ....
ALTER TABLE dbo_Employee AUTO_INCREMENT=16
It gave me "syntax error in ALTER TABLE statement" with highlight on "AUTO_INCREMENT"
Please Help...
Thank you in advance.
I need to change the column's AUTO_INCREMENT value.
I used this query string ....
ALTER TABLE dbo_Employee AUTO_INCREMENT=16
It gave me "syntax error in ALTER TABLE statement" with highlight on "AUTO_INCREMENT"
Please Help...
Thank you in advance.
ASKER
The Modifying of AUTO_INCREMENT will be applied to MSACCESS Database.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your code is working.
I din't know the SQL statment is deferent between MSSQL and MSACCESS.
Thank you so much
I din't know the SQL statment is deferent between MSSQL and MSACCESS.
Thank you so much
This is not directly possible. You can remove the AutoNumber column and add new one which will cause new column values renumbering from 1.
You could also change AutoNumber column data type to Integer and change its values BUT once data were stored Access does not allow to change data type of existing numeric column back into AutoNumber...
SQL to add new AutoNumber column if there is not such column yet:
ALTER TABLE YourTable ADD COLUMN YourColumnName Counter ;
SQL to remove existing column:
ALTER TABLE YourTable DROP COLUMN YourColumnName ;
Work around for setting the Autonumber value other than 1 exists: http://www.databasedev.co.uk/reset_autonumber.html
http://office.microsoft.com/en-us/access/HP051887741033.aspx
http://blogs.techrepublic.com.com/msoffice/?p=111
You could also change AutoNumber column data type to Integer and change its values BUT once data were stored Access does not allow to change data type of existing numeric column back into AutoNumber...
SQL to add new AutoNumber column if there is not such column yet:
ALTER TABLE YourTable ADD COLUMN YourColumnName Counter ;
SQL to remove existing column:
ALTER TABLE YourTable DROP COLUMN YourColumnName ;
Work around for setting the Autonumber value other than 1 exists: http://www.databasedev.co.uk/reset_autonumber.html
http://office.microsoft.com/en-us/access/HP051887741033.aspx
http://blogs.techrepublic.com.com/msoffice/?p=111
Wow. I am testing, trying, etc. I simply believed to above web links...
I've even tested the command:
ALTER TABLE Table8 ADD COLUMN ID Counter(20,10);
and it created numbering 1, 2, 3, etc.
Bloody Access!
... and Angel is just answering the question :-)))
I've even tested the command:
ALTER TABLE Table8 ADD COLUMN ID Counter(20,10);
and it created numbering 1, 2, 3, etc.
Bloody Access!
... and Angel is just answering the question :-)))
ASKER
My work is to sync data with server database.
1. Delete all records in table
2. Insert records from Server.
With 2nd step. the records from server is not order as 1 , 2 ,3 but 16 , 65 , 98 , 103 , ....
so if I delete auto_increment column , create new one to reset it to 1 then I still can't insert record with ID=16 ? ( It will begin at 1 , my first record is not started at 1 but 16)
so Angel's solution can do the job.
1. Delete all records in table
2. Insert records from Server.
With 2nd step. the records from server is not order as 1 , 2 ,3 but 16 , 65 , 98 , 103 , ....
so if I delete auto_increment column , create new one to reset it to 1 then I still can't insert record with ID=16 ? ( It will begin at 1 , my first record is not started at 1 but 16)
so Angel's solution can do the job.
the syntax you posted is for MySQL, not MS Access...
note sure if you can do from code...