Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old January 5th, 2007, 04:52 PM
Registered User
 
Join Date: Jan 2007
Location: , MN, .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to McDiddy
Default Deleting All Record In A Table

I need an easy way (if there is one) to delete all the records in a table. Any Ideas???

Thanks,
Jeff.

  #2 (permalink)  
Old January 8th, 2007, 08:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Use a DELETE query.

Open the query designer, select the table you want to delete records from. Select * as the field (this will select all fields) and then on the menu bar, select Queries > Delete Query.

When you run this query, it deletes all records from the chosen table.

To run this using code (and not throw message boxes) use this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMyDeleteQuery"
DoCmd.SetWarnings True

Now you just have to create a button or other event to run the query.

Did that help?



mmcdonal
  #3 (permalink)  
Old January 9th, 2007, 03:38 PM
Registered User
 
Join Date: Jan 2007
Location: , MN, .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to McDiddy
Default

That worked perfect, thank you!

  #4 (permalink)  
Old January 10th, 2007, 09:20 AM
Authorized User
 
Join Date: Oct 2003
Location: Cleveland, OH, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There's another way to do this using VBA code only (if you'd like):


Dim lngRowsAffected As Long

CurrentProject.Connection.Execute "DELETE * FROM CUSTOMERS", lngRowsAffected


This not only does not require you to create a query object, but you can also check to see how many rows were deleted. I use this type of processing for INSERTs, UPDATEs, etc in a lot of my code without any problems.

In either case, both the .OpenQuery suggestion from mmcdonal and the CurrentProject approaches should work equally well. :)


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting A Record sirmilt BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 July 31st, 2006 07:12 PM
deleting a record from a linked table??? tico31pl Classic ASP Basics 0 May 18th, 2006 09:58 AM
Deleting NULL record in a table dotnet cat SQL Server 2000 5 October 4th, 2005 04:58 PM
Confirm before deleting record jgrasso BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 March 30th, 2005 12:03 PM





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