How do I clear a table in Access with VBA?

Question

What I'm trying to do is, while in Excel, use VBA to push data to an exsisting Access table. I've been able to do this, but am having one small hickup. Before I push the data to access, I want to clear the current data on the Access table, so when the new data from Excel comes in, it is the only data in the Access table. I really don't know how to write code for Access since the class has been on VBA for Excel. I've tried several different approaches and each time it doesn't work. For example, the code that seemed like it should work is

DoCmd.RunSQL "DELETE tblName.* FROM CoversheetTableFourthAttempt

but I get an error telling me to define an object.

If you could help me with this, I would really appricate it

I've put my code below for reference.

Thanks,

Sub AccessFourthMonth()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Users\Kent\Documents\MBA\Winter 2009 Semester\MBA 614\Final Project\shilded\testdatabase.mdb"


' open a recordset
Set rs = New ADODB.Recordset

rs.Open "CoversheetTableFourthAttempt", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet

Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs

        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Project") = Range("A" & r).Value
        .Fields("Description") = Range("B" & r).Value
        .Fields("Amount") = Range("C" & r).Value
        .Fields("Date") = Range("D" & r).Value
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Answer 1

Try

DoCmd.RunSQL "DELETE * FROM TableName"

This article might be of interest: Executing SQL Statements in VBA Code

Answer 2

Try the following from Excel:

dim cn as adodb.connection
dim cmd as adodb.command
set cn = new adodb.connection
cn.open "put your connection string here"
set cmd = new adodb.command
cmd.commandtype = adcmdtext
cmd.commandtext = "Delete * from myTable"
cmd.activeconnection = cn.connectionstring
cmd.execute

Answer 3

Your DoCmd approach has two problems. You used a quote to start a string, but didn't include a closing quote. But even with proper quoting, your DoCmd won't work because Excel does not know that CoversheetTableFourthAttempt is the name of a table in an Access database.

You showed that you can successfully create an ADO connection to your Access database. So my suggestion is to use the Execute method of the connection object to execute your SQL statment:

cn.Execute "DELETE FROM CoversheetTableFourthAttempt;"

Finally, visit Problem names and reserved words in Access to understand why "Date", "Description", and "Project" are not great choices for Access field names.

Answer 4

DoCmd is internal to Access application and not recognized by Excel application. Simple approach to your problem is to fire the delete query from Excel itself.

Add this part after your cn.Open "Provider.. line

cn.Execute "DELETE * FROM CoversheetTableFourthAttempt"

This should clear the table before next part which fills the data runs.