How do I clear a table in Access with VBA?

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

From stackoverflow

• Try

  DoCmd.RunSQL "DELETE * FROM TableName"
  

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

• 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
  

• 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.

  Praesagus : Listen to Hansup and save yourslef much heartache and pain. If someone else has alredy defined the fieldnames and you cannot change them, put brackets around the field names that do not follow best naming practices to get them to work. e.g. [Date] or [Bad Fieldname].

• 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.