Deleting all the data in a table

[Robin Hood]

Hello,

Is it possible to create a query, that will delete all the data in a table. I would like to do this so that I can create a command button on a form that will clear that table of data.

Many thanks

Robin

 

[DCrake]

On your OnClick event on your button enter:


DoCmd.RunSQL "Delete * From [YourTableName]"

If you want to turn off warnings then place

DoCmd.SetWarnings False /True above and below the code.

You may also need to think about putting a message box around it all

If MsgBox("Delete table contents?",vbQuestion+vbYesNo,"Are you sure?") = vbYes Then
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete * From [YourTableName]"
   DoCmd.SetWarnings True
End If

 

[Robin Hood]

Hello DCrake,

Sorry for the lateness of my reply, I have been away.

I have tried to do as you mentioned in the On Click of the properties of the command button on my form. But it comes up with "Microsoft Assess can't find the macro 'DoCmd'.

I have made a delete query that works, but I cannot link that query to the command button on my form and it does not even come up as a choice in the queries list.

Am I doing something wrong?

Many thanks for your help so far

Robin

 

[DCrake]

Paste back the actual code you have behind the OnClick Event of your command button

 

[Robin Hood]

This is what is on the OnClick event of my command button.

DoCmd.RunQuery "Delete * From [Cardboard Letters]"

 

[gemma-the-husky]

there isnt a runquery method

intellisense will offer you (among many others)

openquery, or runsql

so either
DoCmd.openQuery "Delete * From [Cardboard Letters]"

DoCmd.runsql "Delete * From [Cardboard Letters]"

or even

currentdb.execute "Delete * From [Cardboard Letters]"


note that you can also design a delete query in the qbe window, save it as eg "DeleteItems"

and then do

docmd.openquery "deleteitems"

 

[Robin Hood]

Hello Gemma,

Thanks for your reply. I did initially put DoCmd.RunSQL "Delete * From [Cardboard Letters]"
But when that did not work I tried changing to DoCmd.RunQuery "Delete * From [Cardboard Letters]". Sorry DCrake for the wrong info.
But with both I got the same result - "Microsoft Assess can't find the macro 'DoCmd'.

I will try what you said.

Many thanks for your help

Robin

 

[MSAccessRookie]

Hello Gemma,

Thanks for your reply. I did initially put DoCmd.RunSQL "Delete * From [Cardboard Letters]"
But when that did not work I tried changing to DoCmd.RunQuery "Delete * From [Cardboard Letters]". Sorry DCrake for the wrong info.
But with both I got the same result - "Microsoft Assess can't find the macro 'DoCmd'.

I will try what you said.

Many thanks for your help

Robin

Robin,

Are you attempting to place the value directly in the Form Properties like you would a Macro? It sounds like you are trying to use a Macro. The resolution that is being suggested requires creating a Visual Basic Script behind the event. If you need assistance doing that, please let us know.