how to delete all DB rows via OpenRecordset (DAO)

Question

How to delete all rows from table before starting inserting them?

Answer 1

If you are programming vba I assume that you are working with Adodb or simply ADO. Therefore in order to delete a table records you can use a command object to do that.

Dim cnn as Connection
Dim cmd as Command

Set cnn=new Connection()
cnn.ConnectionString="ConnectionString"
cnn.Open()

Set cmd=new Command()
cmd.ActiveConnection=cnn
cmd.CommandText="DELETE FROM MyTable"
cmd.Execute()

cnn.Close()

Updated: In order to use ADO objects you should add a reference to ADODB library

Answer 2

Two things. First, DAO vs ADO is almost irrelevant. They are both available to Access up through Windows 7 and AFAIK there are no plans to remove them. Either should be OK to use in Access. Secondly, you can just do this:

Public Sub Example()
    With Access.CurrentDb
        .Execute "DELETE Table2.* FROM Table2;"
        .Execute "INSERT INTO Table2 ( fld1, fld2 ) SELECT Table1.ID, Table1.MyField FROM Table1;"
    End With
End Sub

You could just to this:

Public Sub Example()
    With Access.DoCmd
        .RunSQL "DELETE Table2.* FROM Table2;"
        .RunSQL "INSERT INTO Table2 ( fld1, fld2 ) SELECT Table1.ID, Table1.MyField FROM Table1;"
    End With
End Sub

But the Execute method throws more informative error messages (and if you still care, is DAO).

Answer 3

DAO Recordsets don't have an effective way to empty the table, as far as I know. If it's a small table, you can probably delete record-by-record, but I would find that silly. I just use DoCmd.RunSQL to run a DELETE query. I would typically set SetWarnings to False temporarily, of course.

Since this is separate from DAO, I would see that operation through before opening the recordset properly.