Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Delete data in a table using vba

    Hi there,
    Does anyone know how I can run VBA code to delete all the data in a table??

    Thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    34
    To delete info in a table, in vba, you're best off using the recordset method...

    dim dbs as dao.database
    dim rst as dao.recordset

    set dbs = currentdb

    set rst = dbs.openrecordset("tablename")

    Then to delete records, find the relevant row in the recordset and...

    rst.delete

    will delete the row, just cycle to delete more rows.

  3. #3
    Join Date
    Sep 2004
    Posts
    112
    Great but I need to delete a hole tables contents with one routine so how would I do that?
    Thanks

  4. #4
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Easier to create a delete query an run that query:
    Code:
    Dim qdf As DAO.QueryDef
    set qdf = CurrentDb.QueryDefs("Query_Name")
    qdf.Execute
    Or do it runtime:
    Code:
    Dim qdf as DAO.QueryDef
    set qdf.Sql = "Delete * from Table_Name"
    qdf.Execute
    Note: running stored query's is faster than running queries generated at runtime.

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    dim toto as string
    toto="Delete * from Table"
    docmd.runsql toto

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And even easier ...

    DoCmd.RunSQL "DELETE * FROM SomeTable;"
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Quote Originally Posted by M Owen
    And even easier ...

    DoCmd.RunSQL "DELETE * FROM SomeTable;"
    If I am right, DoCmd statements don't perform wel. The statement I replied should be faster.
    (However, with the fast pc's of today, you probably even won't notice the difference)
    A good developer is a lazy developer - don't be to lazy, share your knowledge

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Robje73
    If I am right, DoCmd statements don't perform wel. The statement I replied should be faster.
    (However, with the fast pc's of today, you probably even won't notice the difference)
    Ok ... Well then:

    CurrentProject.Connection.Execute "DELETE * FROM SomeTable;",,,AdCmdText

    Since DELETE is an action query it should not take any appreciable time to execute ...

    BTW this example is ADO ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Quote Originally Posted by M Owen
    Ok ... Well then:

    CurrentProject.Connection.Execute "DELETE * FROM SomeTable;",,,AdCmdText

    Since DELETE is an action query it should not take any appreciable time to execute ...

    BTW this example is ADO ...
    Hmm, sounds nice! I keep that in mind.
    BTW, you have to choose between using ADO or DAO otherwise you can get problems with handling recordsets, querydefs and so on.
    A good developer is a lazy developer - don't be to lazy, share your knowledge

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Robje73
    Hmm, sounds nice! I keep that in mind.
    BTW, you have to choose between using ADO or DAO otherwise you can get problems with handling recordsets, querydefs and so on.
    Not necessarily ... As long as you have both libraries referenced ... The only confusion will come from the poor programmer who WILL be taking your name in vain ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •