Access 2007 Empty all Tables

[PanzerAttack]

I'm creating a new database, I have about 100 tables that's updated each AM which act as look ups to provide categories and descriptions for the main information coming in from Sales or Finance etc. I want to empty the tables whilst I'm still building it to check it's all importing but don't want to create loads of quieries to do this.

I Thought something like this might work but I'm obviously missing something, can you please help.

My code:


Public Sub EmptyAllTables()

On Error Resume Next

Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim strSQL_DELETE As String

Set DB = CurrentDb()

For Each TDF In DB.TableDefs

If Left(TDF.Name, 4) <> "MSys" Then
strSQL_DELETE = "DELETE FROM " & TDF.Name & ";"
DB.Execute strSQL_DELETE
End If
Next

End Sub

 

[Joe4]

Very close. You just need an asterisk between the DELETE and FROM.
Also, it is good practice to close your DB reference when done, i.e.

    On Error Resume Next
 
    Dim DB As DAO.Database
    Dim TDF As DAO.TableDef
    Dim strSQL_DELETE As String
 
    Set DB = CurrentDb()
    For Each TDF In DB.TableDefs
        If Left(TDF.Name, 4) <> "MSys" Then
            strSQL_DELETE = "DELETE * FROM " & TDF.Name & ";"
            DB.Execute strSQL_DELETE
        End If
    Next
 
    DB.Close

 

[PanzerAttack]

Thanks for the quick response, I'm obviously missing something.

When I run this Module, it jumps into the code rather than runs it, when I click run, it stops very quickly and no tables are emptied and if I hold down F8, it just runs through the code forever.

Any advice?


Public Sub EmptyAllTables()

On Error Resume Next

Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim strSQL_DELETE As String

Set DB = CurrentDb()
For Each TDF In DB.TableDefs
If Left(TDF.Name, 4) <> "MSys" Then
strSQL_DELETE = "DELETE * FROM " & TDF.Name & ";"
DB.Execute strSQL_DELETE
End If
Next

DB.Close

End Sub

 

[Joe4]

I just created an unbound Form with a command button, and placed the code into the "On Click" event of command button and it worked for me.

 

[PanzerAttack]

Many thanks

 

[boblarson]

Just a quick suggestion - I have a database reset tool (free) on my website which will allow you to exclude your lookup tables which you might not want to populate again and again. So you can select the database, check the box for the ones you want to exclude and click the button to delete those from the list and then save the list so the next time you can just load up the saved selections. Then you click the Green Light button and it will clear out the tables and compact and repair to reset any autonumbers.

It is here:
http://www.btabdevelopment.com/ts/freetools

 

[james_lankford]

is DB.Close correct ?

I have been doing
set DB = nothing
all these years

Have I been doing it wrong

and if DB.Close is correct, what exactly is the difference

 

[boblarson]

is DB.Close correct ?

I have been doing
set DB = nothing
all these years

Have I been doing it wrong

and if DB.Close is correct, what exactly is the difference

DB.Close is fine but it won't do anything as it can't close the current database. Set DB = Nothing will destroy the variable but it won't affect the open database. DB.Close WILL affect any OTHER database if you are using it for one that is not the current database though.