Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 11th, 2008, 09:01 AM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to delete all data from table

What is the best way to delete all the data in a table using code?

I know how to open the recordset and use a while not rec.EOF and delete everything row by row, but is there a way to select all the records and delete en-masse? (like the manual way)

Thanks


 
Old March 11th, 2008, 11:17 AM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I figured it out, create a delete query and then invoke it....



 
Old March 11th, 2008, 12:37 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

There are a couple of ways of doing this depending on where your DBMS is. You can create a local delete query for local tables and then use DoCmd.OpenQuery (after using DoCmd.SetWarnings False, of course), or you can open a recordset on a remote DBMS using "DELETE FROM MyRemoteTable etc" with rs.Open. It looks like you found your own solution though.

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old March 11th, 2008, 12:49 PM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi
Default

You can use the DELETE statement to delete records from table and if you want to delete all records and set the primary to again starting from zero then use the "Truncate Table <table name>" command.



(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is how you got it
 
Old March 11th, 2008, 01:32 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I know you can use that with a SQL DBMS, but I didn't think Access supports it. Do you have any code to invoke this with a Jet DBMS?

This is a good solution unless a column in your table is a foreign key in another table.

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old March 12th, 2008, 09:11 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My favorite way to delete a table is:
DoCmd.RunSQL "delete * from tablename"






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete all the records in a table Kai Lai Access VBA 2 March 15th, 2016 07:20 AM
Can't Modify or Delete table PatYoda SQL Server 2000 3 December 3rd, 2004 09:41 AM
Delete Printers Table Teqlump VB Databases Basics 1 September 10th, 2004 04:45 PM
Delete Table Teqlump Access 6 September 10th, 2004 04:25 PM
How to delete two or more lines in a Table??? thomaz C# 3 July 4th, 2003 02:24 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.