auto delete record button

[nicola1]

Hey.

I have created a button which when clicked imports a table from Excel into Access. Every month the users want to click on this, they want to update the Access table with the updated info in the excel table. My problem is that the whole table is imported again and not just the new rows which have been added in excel. I was just wondering if there was a way to have a button which when clicked it clears all of the records out of my table in access, instead of manually deleting them. So that my users can just click this and then click the import button.

Thanks in advance for your help.

 

[seth_belgium]

Like this:

In this example, the name of your table is tblTable.

Dim SQLEmptyTable

SQLEmptyTable = "DELETE tblTable.* FROM tblTable;"

DoCmd.SetWarnings False
DoCmd.RunSQL SQLEmptyTable
DoCmd.SetWarnings True

This code will empty your entire table without asking confirmation. If you wish to ask confirmation before deleting all records, you should delete the "DoCmd.SetWarning" - lines.

Place this code at the top of your button's OnClick-event.

Seth

 

[ghudson]

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

That command will delete all of the records from [YourTableNameHere] table without any confirmation buttons.

HTH

 

[Big2]

Or use the DoCmd.RunSQL line:

DoCmd.RunSQL "DELETE * FROM YourTableNameHere"

 

[ghudson]

Or use the DoCmd.RunSQL line:

DoCmd.RunSQL "DELETE * FROM YourTableNameHere"

Why? DoCmd.RunSQL will still require the use of the DoCmd.SetWarnings False to avoid the delete records warning.

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

CurrentDb().Execute will delete all of the records from [YourTableNameHere] table "without" any confirmation prompts.

 

[nicola1]

Sorry im confused. Which bit of code should i be using...also on my on-click event where Seth told me to put it, mine goes to the macro which the button is based on. Thanks for both your responses by the way

 

[ghudson]

For the OnClick event of your button you need to click the ellipses [...] button and choose the Code Builder option. That will open the VBA editor for the OnClick event of your button. Then copy and paste the below command. Replace the [YourTableNameHere] with the name of your table. That command will delete all of the records in your table and it will not ask the user if they are sure that they want to delete the records from the table.

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

 

[Birdy]

Why not just use this.

Docmd.setwarning false
docmd.openquery "DELETEQUERYNAME"
YOUR IMPORT ROUTINE
docmd.setwarnings true

 

[ghudson]

Why not just use this.

Docmd.setwarning false
docmd.openquery "DELETEQUERYNAME"
YOUR IMPORT ROUTINE
docmd.setwarnings true

Why waste the space of creating a delete query when you can simply use the CurrentDb().Execute "DELETE * FROM YourTableNameHere" method. Also your method requires the use of the Docmd.Setwarnings False and also Docmd.Setwarnings True which is not neccessary [and another waste of coding] when the CurrentDb().Execute method will do it all with just on little line of code.