Is there simple alternative of sql TRUNCATE

[acteck]

I just need quickly erase all records from the table and reset Autonumber field? is it possible to do without using DAO stuff?
Thank you!

 

[John Big Booty]

Re: Is there simple alternative of sql TRANCATE

Firstly Welcome to the forum.

You can clear a table by simply creating a select query that selects all the records in the table and then changing that query to a Delete query.

To reset your auto number check this article.

Having said that it shouldn't really matter what number the auto number is unless of course you are using that number to represent something meaningful, in which case don't, there are numerous threads concerning the inadvisability of using the auto number for anything other than providing a unique primary key for your data. The oft suggest alternative is to use the Dmax() function +1.

Here's a quick sample you can look at, it's probably a little more complex than what you are currently after but it will give you an idea of what can be done.

 

[acteck]

Re: Is there simple alternative of sql TRANCATE

Thank you John for the quick replay and worm welcome
The reason why i wanted to use TRANCATE , because in SQL Server this command is really fast compare with normal DELETE operations as it is no logging anything in transaction log. So, if we have table with several hundreds of thousands records, i would think delete in Access will take some time to perform, that is why i was thinking about an alternative way to delete everything from a table.
Thank you!

 

[DCrake]

Here is an alternative method.

Create a duplicate of the table with a different name, just the definition, no data.

Create a Make Table query that makes a table from the above table to the real table using the correct name.

Use DoCmd.DeleteObject to delete the table with data in it.

Then run the make table query to recreate the table.

Issue:
If the table is a linked table the make table query needs to point to the back end.

Also you will need to relink the newly created table.

Also you will have to be careful that no other users are using the table at the same time as you are deleting it.

 

[acteck]

Thank you! but it seems that there is no easy alternative, what you have described it is ok, but it is not worth it, so i will stick with the normal delete
thank you !

 

[boblarson]

Re: Is there simple alternative of sql TRANCATE

Thank you John for the quick replay and worm welcome
The reason why i wanted to use TRANCATE...

Umm, that would be trUncate, not trAncate.

 

[acteck]

Re: Is there simple alternative of sql TRANCATE

Umm, that would be trUncate, not trAncate.

yes, it would be TRUNCATE