ADODB removing Records in a recordset

[Aelstro]

I have followed the threads on this forum for some time with great success. Now I am trying to open up a recordset from a precreated table, delete all records in that table, and read in the new ones. Here is the important part of the code: The problem is trying to DELETE the rows without deleting the entire table...

Set DB = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SalesFiles", , adOpenKeyset, adLockOptimistic, adCmdTable

REM Now I need steps to delete rows in sales files

REM this part loads in the new data
Temp = Dir(Location, vbNormal)
Rem rst.Fields(1).Name = "FileNames"
While Temp <> &quot;&quot;
With rst
.AddNew
.Fields(&quot;FileNames&quot = Temp
.Update
Temp = Dir
End With
Wend

Everything works but I can't find any code that works to delete data in rows of a ADODB connection.

Thanks

 

[dvannoy]

theres a couple of ways..one is

DELETE *
FROM table
WHERE blaa blaa

DELETE [table.*]
FROM table
WHERE blaaa

DELETE *
FROM table

 

[Aelstro]

I was afraid it was dreadfully simple and I had just overlooked it.

Thank you!

 

[hongbin81]

Try this:

rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open &quot;Select * from tblLogs&quot;

Do Until rst.EOF
rst.Delete
If Not rst.EOF Then
rst.MoveNext
End If
Loop

rst.Close
Set rst = Nothing

End If
End Sub

I hope that helped.^^

hb

 

[dvannoy]

you can loop through the recordset but I dont think I would...depending on how many records you have it may or may not be slow..

Dim strRs as String

rst As ADODB.Recordset
Set rst = New ADODB.Recordset

strRs = &quot;DELETE * FROM TABLE&quot;

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ,strRs

rst.Close
Set rst = Nothing
End Sub

 

[Aelstro]

Thank you for your help!