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

Philosophaie · Aug 23rd 2012

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

Code

Dim conn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        rs.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\db1.accdb;"
        
              'Delete all records in a table


        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing

Display More

**norie** · Aug 24th 2012

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

Try this.

Code

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset


conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\db1.accdb;"


strSQL = "DELETE * FROM [&lt;TableToDeleteFrom&gt;]"


rs.Open strSQL, conn


conn.Close


Set rs = Nothing
Set conn = Nothing

Display More

Smallman · Aug 24th 2012

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

Philosophaie · Aug 24th 2012

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

**norie** · Aug 24th 2012

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.

Code

Sub RunQuery()


Dim ao As Object
Dim strSQL As String


    strSQL = "DELETE * FROM [Table2]"


    Set ao = CreateObject("Access.Application")
    
    ao.OpenCurrentDatabase ("C:\database2.accdb")


    ao.DoCmd.RunSQL strSQL


    ao.Quit


End Sub

Display More

Philosophaie · Aug 24th 2012

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.

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

Participate now!

Share