Delete Table Data using button on Form

[mattc66 via AccessMonster.com]

I need to delete the data in 10 tables prior to uploading new data. I would
like to have this as a button on a form. I have created the button and added
the click event. When I add the SQL statement I get lost in the syntax.

In looking at other help on this site – I found how to execute a SQL
statement in VB:
DoCmd.RunSQL

In a query I see the proper syntax to Delete the Data as follows:

DELETE tableName.*
FROM tableName;

When I put this all together in VB it fails.

How do I run a SQL Delete statement in VB. I will need to repeat this
statement for each table.

I know I could do this with a Query and then do a DoCmd.RunQuery – I wanted
to reduce the query step.

Thanks
Matt

 

[John Spencer]

Dim strSQL as String

strSQL = "Delete FROM [TableNameA]"
DoCmd.RunSQL StrSQL

strSQL = "Delete FROM [TableNameB]"
DoCmd.RunSQL StrSQL

strSQL = "Delete FROM [TableNameC]"
DoCmd.RunSQL StrSQL
....

 

[Denny]

Hi Matt

I created a table and a form with a command button. The following code
deletes all the data out of the table 'employerphonenumber'
Notice no semicolon. Does that help your error?


Private Sub Command3_Click()
On Error GoTo ErrorHandler
DoCmd.RunSQL "delete employerPhoneNumber.* from employerPhoneNumber"

ErrorHandler:
If 2501 Then 'User pressed Cancel
Resume Next
End If
End Sub

 

[mattc66 via AccessMonster.com]

Thanks Denny that is what I needed.

Matt

Hi Matt

I created a table and a form with a command button. The following code
deletes all the data out of the table 'employerphonenumber'
Notice no semicolon. Does that help your error?

Private Sub Command3_Click()
On Error GoTo ErrorHandler
DoCmd.RunSQL "delete employerPhoneNumber.* from employerPhoneNumber"

ErrorHandler:
If 2501 Then 'User pressed Cancel
Resume Next
End If
End Sub

I need to delete the data in 10 tables prior to uploading new data. I would
like to have this as a button on a form. I have created the button and added

[quoted text clipped - 19 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Thanks John this will work great..

Matt

Dim strSQL as String

strSQL = "Delete FROM [TableNameA]"
DoCmd.RunSQL StrSQL

strSQL = "Delete FROM [TableNameB]"
DoCmd.RunSQL StrSQL

strSQL = "Delete FROM [TableNameC]"
DoCmd.RunSQL StrSQL
...

I need to delete the data in 10 tables prior to uploading new data. I would
like to have this as a button on a form. I have created the button and added

[quoted text clipped - 23 lines]

Matt Campbell
mattc (at) saunatec [dot] com