Clear all Records

[klwu]

Hi all,

I have a T_Log table in my database, which basically records all the log histories of users. I want to create a command button where the user can delete all the log records. How should I write the code? Thanks.

 

[WayneRyan]

kwlu,

DoCmd.RunSQL "Delete * From T_Log"

Wayne

 

[klwu]

That's great, Wayne.

Thanks a lot.

 

[Mile-O]

Create a delete query insetad and Open it.

With DoCmd
    .SetWarnings False
    .OpenQuery "qryDeleteLogs"
    .SetWarnings True
End With

Although it is more lines than Wayne's solution, it has the added benefit that it doesn't have to create a delete query each time you run the code. As it stands now Wayne's code will have your database size bloating unnecessarily each time your run the code.

 

[Pauldohert]

Can someone explain the bloat - I imagined the database to be constantly growing each time SQL was used from code, so better to use a query.

Does the database grow - and not shrink again - I haven't noticed it in my applications the database is the same size it was when it was installed?
(mde using runtime)

What impact does the bloat have??

For a static query - fine make a query - for dynamic queries is it better to make a querydef than deal with the bloat or is it basically doing the same thing??

Thanks

 

[Mile-O]

Have a look at this.

 

[Pauldohert]

Thanks will do.

 

[Pauldohert]

Hmm very interesting - tried the example and yes it grows 80kb ish each time I run it.

Have tried other things

Private Sub Form_Open(Cancel As Integer)
Dim wSQL As String
Dim i As Long
For i = 1 To 3000
Me.RecordSource = "SELECT * FROM TEST WHERE ID = " & i & ";"
Next
End Sub

Sub DeleteSomething()
Dim wSQL As String
Dim i As Long
For i = 1 To 3000

wSQL = "DELETE * FROM Test WHERE ID = " & i & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL wSQL
DoCmd.SetWarnings True
Next

End Sub

and no noticeable change in size occurs - is it because I am not saving the change? ie my delete code statement never "existed" as a querydef so it is not creating space as it alters?? Similarly for the recordsource code??

 

[ghudson]

I just did a quick comparision of a delete query versus the below Currentdb().Execute and I did not see any difference in regards to the bloat.

Currentdb().Execute "DELETE * FROM YouTableNameHere"

 

[Pauldohert]

I am more confused than ever so

wSQL = "DELETE * FROM Test WHERE ID = " & i & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL wSQL
DoCmd.SetWarnings True

and

Currentdb().Execute "DELETE * FROM YouTableNameHere"

are ok and don't bloat??

The same for recordsources?