Delete all records from a table

[trystanhuwwilliams]

Hi,
Can someone tell me how u can delete all the records from
a particular table without opening it, using a command
button from an unbound form?

Thank you

 

[nberryman]

Create a Delete Query, and then run this from the command button on a form

Make sure that you get the criteria right as all will be lost

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[djsiders]

I use the following hard coded database clearing routine in DAQ equipment that will collect data for a given test time & then it is no longer needed.


Private Sub pwdClearDatabase_Click()

Dim strConnectionString As String
Dim strFileSpec As String
Dim strDataSource As String
Dim rstMain As ADODB.Recordset
Dim intResp As Integer


' Confirm data deletion.
intResp = MsgBox("Clear Current DataBase Records?", vbYesNo + vbCritical, "Clear Database&quot

If (intResp = vbYes) Then
strFileSpec = App.Path & "\DbName.mdb"

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileSpec & _
";Persist Security Info=False"

Set rstMain = New ADODB.Recordset
rstMain.Open "DELETE * FROM MainDataTable", strConnectionString, adOpenKeyset, _
adLockOptimistic, adCmdText

Set rstMain = Nothing ' Close record set.
Call CompactDatabase ' DB compact & repair sub

MsgBox "DataBase Cleared"

End If

End Sub

 

[JPeters]

Continuation from Nberryman's Post...

Trystan,
I would definitely create a delete query. When you make your query, have it get data from the table you would like to clear. You will see a star at the top of the table's field list. Take that start and drag it down to your query design in one of those slots. Now up on the query toolbar, change it to a delete query (it's a select query now). Go ahead and run it to test it out. It will tell you how many records you are going to be deleting.
Step 2!
In your form, create a button, go ahead and have it run your delete query - you can use the wizard or do it yourself. Now if you don't want to see an annoying popup each time you push taht button and run the query you should set warnings to false by entering the button's properties/on click EVent in the Form's Class Module...
Add this.

DoCmd.Setwarnings = False
DoCmd.OpenQuery "qryDelete" ' this will already be there
DoCmd.SetWarnings = True

This will run it w/out warning you about deleting the records.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------