Delete all data in certain tables

snyperj
snyperj used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
open the table "tbl_temp_tables" as recordset

dim rs as dao.recordset
set rs=currentdb.openrecordset("tbl_temp_tables")
rs.movefirst

do until rs.eof
   currentdb.execute "delete * from " & rs.tableName
rs.movenext
loop
Top Expert 2010

Commented:
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

Top Expert 2016

Commented:
typo error

change this

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

with

currentdb.execute "delete * from " & rs!tableName
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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
Top Expert 2010

Commented:
Forgot the MoveNext. Tres embarrassing :)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
Thanks- this worked so it is what I used.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial