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

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.

Code:

---

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

---

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

Code:

---

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

Conn.Execute strSQL

---

The way you are doing it is very long winded,

why not just run a truncate table command;

vb Code:

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

If you are using Access VBA you can just do a

Code:

---

DoCmd.RunSQL("DELETE FROM yourtable")

---

Thanks guys,

Tried all three methods and they work.

Quote:

---

Originally Posted by Torc

Thanks guys,

Tried all three methods and they work.

---

Hi all

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