How to truncate the table of access database using vb.net

Question

0.00/5 (No votes)

See more:

Hi friends, I have a requirement in vb.net . I want to delete the records in table of access database. If i want to insert the records from starting point. But in that table is identity column. records are not inserted from starting position. If i wrote the Truncate query in vb.net .I got the error in program.

error is : Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Please Help me...

Posted 17-Jul-13 2:10am

CH Guravaiah

Updated 17-Jul-13 2:31am

v2

Add a Solution

2 solutions

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)

**Zoltán Zörgő** · Answer 1 · 2013-07-17T02:19:00

Solution 1

There is no such thing as "starting point", neither is a "truncate statement" in this sense. Those are DBase concepts.
I assume you have a quite old Access - according to my experience, some versions ago the autonumber fields were only 2 bytes wide, thus enabling only a maximum of 65565 records. Currently there are 4 bytes, thus you have a quite large number - so I don't think you need to reset the numbering.
You can however delete records, if you think your table became too large. But the newly inserted records will continue with the autonumbering, you can not reuse them. If you need such thing, you will need a field that is not autonumbered, and you have to do the numbering logic yourself - which can be complicated with access if there are concurrent connections involved.

Posted 17-Jul-13 2:19am

Zoltán Zörgő

Updated 17-Jul-13 2:21am

v2

Comments

CH Guravaiah 17-Jul-13 8:31am

ok but how can do that using vb.net in my form.

Zoltán Zörgő 17-Jul-13 8:51am

1) Tell me what Access version are you using.
2) Tell me if there is concurrent usage of the database, or there is only a single user at a time?

We can talk about all others after you answer these.

Maciej Los 17-Jul-13 8:53am

There are several ways to reset autonumber field. See my answer ;)
BTW: i would never suggest to replace autonumbering with custom numbering ;(

Zoltán Zörgő 17-Jul-13 8:58am

I haven't suggested to replace it, just to add an other field, that is "resetable". Still, I don't think that your suggestion is applicable in a running system, most of them begin with: "Delete the AutoNumber field from the main table.".

Maciej Los 17-Jul-13 9:11am

Now i understand what you'd pointed... I think we are talking about 2 worlds. You're talking about: "how to start new portion of numbering" and i'm talking about "how to reset autonumber field". I need to be honest, in this case,you are more comprehensive ;)
5!

Zoltán Zörgő 17-Jul-13 9:21am

You might be right. Actually the concepts used by OP are a little bit outdated even form access point of view... so I am not sure I have got the right view, or you got it. So your answer is also worth a 5!

Maciej Los 17-Jul-13 9:23am

Thank you, Zoltan ;)

Maciej Los · Answer 2 · 2013-07-17T02:45:00

Solution 2

I think you want to truncate table because of need to reset autonumber property for key field (identity in MS SQL server). There is no functionality in MS Access, but there are several ways to achieve that. More about it you'll find here: How to reset an AutoNumber field value in Access[^]

Posted 17-Jul-13 2:45am

Maciej Los

Updated 17-Jul-13 2:46am

v2