[RESOLVED] [Access 2003] Deleting all rows in a Table

[Torc]

I have a Table in my Db which I use for holding information generated during a run.

One of the requirements is that the Table be blank at the start of a run.

I am currently using the following, however it has two flaws.

One is that it takes a long time to run, the second is that after approx 7,000-
9,000 deletions I get a file sharing lock count exceeded message.

Can anyone advise as to a better/quicker way to delete all the rows in a table.

Sub DeleteAllRowsInTable(ByVal TableName as string)
Dim RS As ADODB.Recordset
Dim SqlStr As String
Dim Counter As Long
Dim StartTime As Date
    StartTime = Now
    
    SqlStr = "Select * from " & TableName

    Set RS = New ADODB.Recordset 'as we did with the connection
    RS.Open SqlStr, CurrentProject.Connection, adOpenKeyset, adLockPessimistic, adCmdText 'opening the recordset explained in the notes
       
    RS.MoveFirst 'moves to the first record
    Counter = 1
    Do Until RS.EOF = True 
        DoEvents
        Debug.Print Format(StartTime, "hh:nn:ss") & "  " & Format(Now, "hh:nn:ss") & "  " & Counter
        RS.Delete
        RS.Update
        RS.MoveNext 'moves next record
        Counter = Counter + 1
    Loop
    RS.Close
    Set RS = Nothing
End Sub

[GaryMazzone]

Why are you walking trough a recordset to perform a delete? Similar to issue a delete command

Dim strSQL as String
strSQL = "Delete From " & tableName
Now open your connection and execute on it

Conn.Execute strSQL

[NeedSomeAnswers]

The way you are doing it is very long winded,

why not just run a truncate table command;

If ResetTable(tablename) = True then
 'do stuff
End If
 
 
Public Function ResetTable(TableName as string) As Boolean
 
On Error GoTo errorhandler
 
Set adocom = New adodb.Command
 
 
adocom.CommandText = "Truncate Table " & TableName
adocom.CommandType = adCmdText   ' set the command type
 
adocom.ActiveConnection = CurrentProject.Connection' set the active connection 
adocom.Execute
 
 
ResetTable = True
 
Set adocom = Nothing
 
 
Exit Function
errorhandler:
    ResetTable = False
 
End Function

[Hack]

If you are using Access VBA you can just do a

DoCmd.RunSQL("DELETE FROM yourtable")

[Torc]

Thanks guys,

Tried all three methods and they work.

[shahkal]

Thanks guys,

Tried all three methods and they work.

Hi all

This information is also helpful to me.............thanks to all guys.....................