asked on

Delete all data in certain tables

If I created a table (tbl_temp_tables) of table names, Is there a way I could then run an event that would delete all records in all of the tables I have listed in tbl_temp_tables?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Yes. Just add the following snippet to, say, the click event of a command button:

Dim rs As DAO.Recordset
Dim TblName As String
Dim sql As String

DoCmd.SetWarnings False

Set rs = CurrentDb.OpenRecordset("tbl_temp_tables")
Do Until rs.EOF
    TblName = rs!Table_Name
    sql = "DELETE * FROM [" & TblName & "]"
    DoCmd.RunSQL sql
Loop
rs.Close
Set rs = Nothing

DoCmd.SetWarnings True

Open in new window

Rey Obrero (Capricorn1)

typo error

change this

currentdb.execute "delete * from " & rs.tableName

with

currentdb.execute "delete * from " & rs!tableName

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Private Sub btnDelData_Click()
With CurrentDb.OpenRecordset("tbl_temp_tables", dbOpenDynaset)
Do Until .EOF
CurrentDb.Execute "DELETE * FROM " & ![TableName]
.MoveNext
Loop
End With
End Sub

Make a BACKUP first ...

mx

Patrick Matthews

Forgot the MoveNext. Tres embarrassing :)

Jeffrey Coachman

<No points wanted>

Whenever I do anything "Destructive" I always feel that it is a good idea to wrap it in some type of "Confirmation" code.
Something roughly like this:

If msgbox("Delete all records?, Are you sure?", vbquestion+vbyesno)=vbno then
Exit Sub
end if
'Your Delete code

JeffCoachman

snyperj

ASKER

Thanks- this worked so it is what I used.