create a Recordset and have it delete all records in an Access table

  • In Excel VBA I would like to create a Recordset and have it delete all records in an Access table keeping the fields.


  • Re: create a Recordset and have it delete all records in an Access table


    Try this.


  • Re: create a Recordset and have it delete all records in an Access table


    Hi


    I tend to set up a delete query in my access database and go to this place.


    Code
    Option Explicit
    Sub RunQuery()
        Dim ao As Object
        Set ao = CreateObject("Access.Application")
        ao.OpenCurrentDatabase ("D:\Copy of FinancialControlReport.mdb")
        ao.CurrentDb.QueryDefs("qryDelData").Execute
        ao.Quit
    End Sub


    Take care


    Smallman

  • Re: create a Recordset and have it delete all records in an Access table


    I am getting an error on the rs.Open line:


    Quote

    "The connection cannot be used to perform this operation. It is either closed or invalid in this context."


    Code
    strSQL = "DELETE * FROM [Table1]"
     rs.Open strSQL, conn
  • Re: create a Recordset and have it delete all records in an Access table


    I think I've got the syntax slightly wrong or I'm missing something - I'll check that.


    In the meantime, if you do want to use Smallman's idea of opening the database you could use DoCmd.RunSQL.


  • Re: create a Recordset and have it delete all records in an Access table


    Could you add something to it "ao." open the Access Application to the desktop and open to Table1 after the Delete is complete.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!