how to truncate table in access.

Question

1.00/5 (1 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 12-Jun-12 23:43pm

Prosan

Add a Solution

Comments

Prosan 13-Jun-12 6:37am

how can we add a autonumber field in table by query.

3 solutions

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

Posted 12-Jun-12 23:59pm

Nileshbhai

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.

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

Posted 13-Jun-12 1:27am

Nileshbhai

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prosan · Accepted Answer · 2012-06-13T01:18:00

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:-

SQL

delete from  TableName
alter table TableName alter column Id number  
alter table TableName alter column id AUTOINCREMENT

Posted 13-Jun-12 1:18am

Prosan

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