How do you delete the contents of a table using VBA?

[mikewood1980]

Hi

I would like to delete the contents of one of my tables in the database using code. Does anyone know how to do this. Table is already declared with the following code...

    Dim dbCurr As Database
    Set dbCurr = DBEngine.Workspaces(0).Databases(0)
    Dim rsTableTest As Recordset
    Set rsTableTest = dbCurr.OpenRecordset("tblTableTest")

    'need to delete the table here ***********

can anyone help?

Thanks
MikeWood

 

[DJkarl]

Hi

I would like to delete the contents of one of my tables in the database using code. Does anyone know how to do this. Table is already declared with the following code...

    Dim dbCurr As Database
    Set dbCurr = DBEngine.Workspaces(0).Databases(0)
    Dim rsTableTest As Recordset
    Set rsTableTest = dbCurr.OpenRecordset("tblTableTest")
 
    'need to delete the table here ***********

can anyone help?

Thanks
MikeWood

You would be much better off to use a SQL statement in code to delete your records. If you are deleting everything I'd use something like.

dbCurr.Execute "DELETE * FROM tblTableTest"

Cycling through a recordset just to delete records would be wasted effort, now if you need to do that for some unexplained reason then you could use something like

Do Until rsTableTest.EOF
     rsTableTest.Delete
     rsTableTest.MoveNext
Loop

 

[khawar]

Simply use one line code

USING ADO
currentproject.connection.execute"delete * from tblTableTest"

Or

USING DAO
currentdb.execute"delete * from tblTableTest"

 

[FoFa]

If it is everything, I would create a delete query, then use DOCMD to run it. much easier to maintain.