Click here to Skip to main content
14,332,032 members
Rate this:
Please Sign up or sign in to vote.
See more:
hello i have a table in access and it has a autonumber field. when i import data from excel than i delete all data from this table and insert data from excel to in this table but in autonumber field it start from it's last number. i want to start it to 1. i have used truncate table command but it is not working in access 2003.
Posted
Comments
Prosan 13-Jun-12 6:37am
   
how can we add a autonumber field in table by query.
Rate this:
Please Sign up or sign in to vote.

Solution 1

prosan,

i refer your problem, you attempting to delete all records from the table and again starts the autonumber from 0.
it is not possible to start from 0, other wise you can do delete the table and recreate it. so your problem of deleting and autonumber, both will be solved.
because, this is the default behaviour of the database for autonumber fields to increment every time when the record insert, it cannot be set by the user.

so, choice is yours either you delete the table and recreate the table , it will gives from the 0 autonumber.


thanks,
Nilesh
   
Comments
Prosan 13-Jun-12 6:36am
   
if i delete table than now how i add autonumber field in this table. what i will write in query which will add autonumber field in table.
Rate this:
Please Sign up or sign in to vote.

Solution 3

hi Prosan,


yes, you can do CRUD operation in access tables using queries.

for alter table fields :
Alter TABLE <tablename> alter <columnname> COUNTER

This will update table fields to autonumber.

here, you will find the columns datatype will be change to AutoNumber.


for more information visit http://allenbrowne.com/func-DDL.html#CreateTableDDL[^]

Thanks,
Nilesh
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

hello i have found the solution and now i am giving solution
first delete all data than convert this field datatype to number than again convert it datatype to autoincrement. here is simple example:-
delete from  TableName
alter table TableName alter column Id number  
alter table TableName alter column id AUTOINCREMENT
   
Comments
Siva Krishna 9-May-17 8:10am
   
I have added this code it's working for normal tables.
but sharepoint linked tables are not working also i'm getting error message like "cannot execute data definition statements on linked data sources".Please help me its very urgent

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100